Skip to Content
author's profile photo Former Member
Former Member

What is teh correct Syntax for command?

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';

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jan 23, 2014 at 07:06 PM

    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


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 23, 2014 at 07:09 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Dell Stinnett-Christy

      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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.