What is teh correct Syntax for command?

[Former Member]

I have a MYSQL Query that is I am trying to use in CR but it is not returning NOTHING. This ia working MySQL query as I have built a PHP application for it. So what is the syntax to get the MySQL to work?

Here is my MySQL query:

SELECT SUM(a.paidForProc) AS paidForProc, SUM(a.overPayment) AS overPayment

FROM patient_charges_detail_payment_info a

LEFT JOIN patient_chargesheet_payment_info b ON b.payment_id = a.payment_id

LEFT JOIN patient_charge_list c ON b.encounter_id = c.encounter_id

LEFT JOIN patient_data e ON c.patient_id = e.id

WHERE b.transaction_date LIKE '2013-01-23'

AND a.deletePayment=0

AND c.del_status="0"

AND trim(e.lname) != 'doe'

AND b.paymentClaims NOT LIKE 'negative payment';

[former_member11696]

Hi Christian,

Easiest way is to create a new report and add the tables, drop those fields onto the report and then add the filtering in the record selection formula.

Keep it simple to start off with....

Then view the SQL statement, copy and paste that into a new report Command box.

Typically CR requires quotes around the fields to qualify the case.

Don

[Former Member]

I need to use commands because the database I am working with has incompatible ids between two or more tables. See my post above with screen capture for the issue I am having with commands.

[DellSC]

For testing purposes, if you just put the patient_charges_detail_payment_info table in the report without putting it in a command, do you get any data?  If not, the issue is in the configuration of your connection to the database and not anything specific to Crystal.

-Dell

[Former Member]

Okay this is getting very frustrating. The connection is fine since I was able to get a simple query to work: SELECT * FROM patient_charges_detail_payment_info  BUT in order to do so I had to create a NEW report. But this query is still returning nothing:

SELECT SUM(a.paidForProc) AS paidForProc, SUM(a.overPayment) AS overPayment

    FROM patient_charges_detail_payment_info a

    LEFT JOIN patient_chargesheet_payment_info b ON b.payment_id = a.payment_id

    LEFT JOIN patient_charge_list c ON b.encounter_id = c.encounter_id

    LEFT JOIN patient_data e ON c.patient_id = e.id

    WHERE b.transaction_date LIKE '2013-01-23'

        AND a.deletePayment=0

        AND c.del_status="0"

        AND trim(e.lname) != 'doe'

        AND NOT (b.paymentClaims LIKE 'negative payment')

Please help.

[DellSC]

I understand your frustration.  At this point I would start breaking down the query.  We know that you get data when patient_charges_detail_payment_info is included in the report.  I would then add one join at a time and test the query after adding each one to make sure that the data is still coming back.  I would then start adding the Where clause back in, one line at a time, testing between each one to make sure you still have data.  Somewhere in this process, you'll lose the data and you'll find out exactly which line is causing the issue.  From there you can look at what changes need to be made to that specific line in order to get data again.

This is the process I go through when I'm having this type of problem with a query (and I use commands almost exclusively in my reports because I can write more efficient SQL than either Crystal or a universe can).

-Dell

[Former Member]

Thank you. I found the problem. I had to create a new report because when I modified the command query from a saved report I did not see any change or results. I had to create a new report with the query in order to get results. I do not know why but that is what I had to do. Very inefficient and annoying. Not a great way to create queries. I find it soo much better to just create applications with PHP and MySQL and I can do whatever I want without limitations. I find Crystal Reports very limited and stubborn.

[former_member11696]

When using Commands CR does nothing to it, if it is not formatted for the DB you are using it won't run, not becaise of CR but because of the client you are using.

PS - check off Saved Data under the file menu then it will run when you hit the refresh button with the new SQL.

Don

[DellSC]

I usually develop the SQL for a report in some other tool - (Toad, SSMS, etc.  depending on the type of database I'm connecting to) because it's usually easier to debug it there and then paste it into Crystal without parameters and save to verify that it works in Crystal.  I'll then edit the command and add any required parameters.  I've been working with Crystal for a long time (>18 years) and I prefer to use commands for my reports.  This is the method that I've found works the best for me.

You also have to bear in mind that some SQL that works with a native or OleDB connection may not work in an ODBC connection due to limitations in some ODBC drivers.

-Dell

[Former Member]

Correct, that is how I create my queries as well. All the queries I am using in CR have already been built for a bunch of PHP and MySQL applications that I wrote, so these are working MySQL queries. I need to get this working in CR. So the thing is I am learning CR and that is why I am having all this trouble and I do find CR somewhat inflexible to work with. For another example: I need to be able to do multiple queries for a report, which I posted in a new thread.

[DellSC]

Have you tried pasting this exact query into the Command Editor in Crystal - minus the ";" at the end?  One other change I might make is for the last line:

AND NOT (b.paymentClaims LIKE 'negative payment')

I've found that SQL can be funny about how it handles "Not Like" depending on both the database and the type of connection (I assume your using ODBC...)

-Dell

[Former Member]

I tried that still returns nothing. I also tried a very simple query:

SELECT * FROM patient_charges_detail_payment_info

and this also returns nothing.

Command query:

Results of command query: