Crystal Reports Command (SQL Query)

[mark12010]

I have a Crystal XI report that list all clients for a specified outpatient (recurring episode) program, last appointment, who they saw, and how many days since they were last seen. I couldnt get it to group properly to get the dadt the way I wanted it. It was suggested that I use an SQL Command. I am pulling data from an Intersystems Cache 5.0 database. I have never realy worked with SQL commands in crystal after some research this is what I came up with:
select
EP.patid
, EP.v_patient_name
, EP.episode_number
, EP.date_of_discharge
, EP.preadmit_admission_date
, EP.program_value
, APPQUERY.AppointmentStart
, APPQUERY.service_code
, APPQUERY.staffid
, APPQUERY.staff_name
, APPQUERY.site_name
, APPQUERY.MaxAppt

from episode_history EP
left outer join
( select
APP.PATID
, APP.episode_number
, cast(string(cast(APP.appointment_date as varchar), ' ', cast(convert(time, APP.appointment_start_time) as varchar)) as datetime) as "AppointmentStart"
, APP.service_code
, APP.staffID
, APP.staff_name
, APP.site_name
, MAXAPP.MaxAppt
from
appt_data APP
inner join
(select
patid
, episode_number
, max(cast(string(cast(appointment_date as varchar), ' ', cast(convert(time, appointment_start_time) as varchar)) as datetime)) as "MaxAppt"

from appt_data
where program_code = '{?Program}'
group by patid, episode_number) as MAXAPP on
((APP.patid = MAXAPP.patid) and (APP.episode_number = MAXAPP.episode_number) and (cast(string(cast(APP.appointment_date as varchar), ' ', cast(convert(time, APP.appointment_start_time) as varchar)) as datetime) = MAXAPP.MaxAppt))

) as APPQUERY on ((EP.patid = APPQUERY.patid) and (EP.episode_number = APPQUERY.episode_number))


where
EP.program_code = '{?Program}' and
EP.date_of_discharge is null

When I try to run it I am getting several errors:
1) Failed to retrieve data from database
2)

Im sure my syntax is all messed up can anyone shed some light as to what I may be doing wrong.
Thanks

 

[IanWaterman]

Have you checked each of the Subqueries in some sort of database mangement tool.

Failing that run each query in a command on crystal and make sure it executes.

Also instead of going straight in with a Crystal Command parameter hard code a program name

I have never seen the command parameter used like this

where program_code = '{?Program}'

It can not be wrapped in ' ' if you need a string parameter I think you must create as string and not try and convert to string by wrapping in ''

Command parameters are different to report parameters and MUST be created in the Command window you can not use existing report parameters, they should be deleted from report if they exist unless they are being used exclusively in report and not for filtering data.

Ian

 

[mark12010]

The only thing i dont understand is A collegue from another hospital is running the same system except he is running a newer version of Cache and it works as intended on his system. That is what makes me think it is a syntax issue. I am about ready to give up on the SQL command and just try re-writing the report in Crystal without using Command. I am at a loss.

 

[jmd0252]

do you have anything in your database manager, that would allow you to create a "view"? That way you can create the view and "see", the underlying sql atatement.

 

[mark12010]

do you have anything in your database manager, that would allow you to create a "view"? That way you can create the view and "see", the underlying sql atatement."
Yes it is the same as the code in the SQL command that I posted above.
There was also a Parameter at the same time as the command under the add command feature.

 

[mark12010]

Can anyone please help me out here I am kinda under the gun to complete this report
Thanks in advance

 

[lbass]

The error message seems to suggest that there is a close paren where an open one is expected. I would comment out various parts of the query to see which section is causing the problem.

-LB

 

[RTag]

You have this piece of code :

MAX(
CAST(
string(
CAST(appointment_date AS VARCHAR),
' ',
CAST(CONVERT(TIME, appointment_start_time) AS VARCHAR)
) AS DATETIME
)
) AS "MaxAppt"

I am not familiar with the SQL syntax used in Intersystems Cache database, but looks like you are concatenating the strings for date and time and then converting the result to datetime. This conversion will most likely be dependent from your system default date format and may be the reason why the SQL works on another computer and doesn't work on yours.
Could you try to set your system regional setting for short and long date and time to be the same as those on the system where this SQL works. ( I guess you should do this on the server where the database is installed not on your local computer)
The other option is to try to SUM the date and time fields directly MAX(appointment_date + appointment_start_time) As MaxAppt - I am not sure that this is supported by the database, but usually date and time are presented as real numbers so operation should be possible. BTW If the direct sum for date and time is possible the report will be faster because you will avoid 9 conversions per record

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.