Parameter and SQL Query

[maruja]

Greetings:

I work at a medical facility. I have established 2 parameters. One for start date, the other for end date. This is being used on quarterly reports. I am trying to insert this is an SQL query. Here is the logic:

(Select sum(A."visits") from SMSDSS.c_cmb_vst_v_20050303 A
where A."orgz_short_name" = c_cmb_vst_v_20050303."orgz_short_name" and
A."drg_no" = '127' and
A. "ind_readm_30_samedrg"= 1 and
A. "vst_type_cd"= 'i' and
A. "dsch_date" = '{?Start Date}' and
A."dsch_date"= '{?End Date}')

Basically, what is says is sum (visits) from the visits table. I am qualifying on DRG (diagnosis related group) 127 and if the patient was readmitted for the same drg within 30 days and if the patient had a type code of i which is inpatient and the discharge date (Start date) is 01/01/2004 and the discharge date (End date) is 03/31/2004. However, with the insert of the parameter in my SQL I get an error message. (Syntax error. No rowset was returned for this query, procedure or table.) I am on Crystal 8.5.
Thank you.

 

[jpdmdc]

I think the parameters need not be in quotes!

 

[maruja]

jpdmdc:

I tried it without quotes and I received the same error message. Any other thoughts??
Thank you. Maruja

 

[jpdmdc]

Hi Maruja,

I think the logic near the dates should be changed.

It should be
(A. "dsch_date" = {?Start Date} OR
A."dsch_date"= {?End Date}) ).

Otherwise it could be
A. ("dsch_date" between {?Start Date} and {?End Date})


Depending on your requirement use one of the above and see.It might work. By the way, what database are you using?

 

[maruja]

jpdmdc:

I tried both your suggestions and neither worked. I appreciate your help. Thanks. Maruja

 

[lbass]

Where are you creating this subquery? Parameters cannot be used in SQL expressions, if this is where you are creating the subquery. Nor can you build them into the "Show SQL query" area in 8.5. Even so, if you COULD, you would need to change the date part of the query to:

and
A."dsch_date" >= {?Start Date} and
A."dsch_date" <= {?End Date}
)

Right now, your query is attempting to equate one field to two different dates.

-LB

 

[maruja]

lbass:

I am trying to put the parameters in the sql expression editor. From your reply you've stated I can't do that. Is that correct?

 

[lbass]

Yes. One way around this is if you can define a standard date range that you want to pull into the report, e.g., month, you could have the SQL expression pull back a summary based on a month, and then you could limit the values in the record selection formula. So you could for example, use a SQL expression like:

(
Select sum(A."visits") from SMSDSS.c_cmb_vst_v_20050303 A
where A."orgz_short_name" = c_cmb_vst_v_20050303."orgz_short_name" and
A."drg_no" = '127' and
A. "ind_readm_30_samedrg"= 1 and
A. "vst_type_cd"= 'i' and
{fn month(A. "dsch_date")} = {fn month(SMSDSS.c_cmb_vst_v_20050303."dsch_date")} and
{fn year(A. "dsch_date")} = {fn year(SMSDSS.c_cmb_vst_v_20050303."dsch_date")}
)

Then in the record selection formula you could use:

month({SMSDSS.c_cmb_vst_v_20050303.dsch_date}) = {?month} and
year({SMSDSS.c_cmb_vst_v_20050303.dsch_date}) = {?year}

Only the summary values from the SQL expression that met these criteria would be returned.

-LB

 

[maruja]

Thank you, lbass.

Maruja