Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Crystal Reports Command (SQL Query)

Crystal Reports Command (SQL Query)

Crystal Reports Command (SQL Query)

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:
, 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

from episode_history EP
left outer join
( select
, 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
appt_data APP
inner join
, 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))

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

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

RE: Crystal Reports Command (SQL Query)

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.


RE: Crystal Reports Command (SQL Query)

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.

RE: Crystal Reports Command (SQL Query)

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.

RE: Crystal Reports Command (SQL Query)

"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.

RE: Crystal Reports Command (SQL Query)

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

RE: Crystal Reports Command (SQL Query)

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.


RE: Crystal Reports Command (SQL Query)

You have this piece of code :

CAST(appointment_date AS VARCHAR),
' ',
CAST(CONVERT(TIME, appointment_start_time) AS VARCHAR)
) 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

www.R-Tag.com Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close