Date in SQL Query is always today's date

[wreiche]

I am using crystal reports 11. I am using the database expert to create a virtual table using an SQL command. I am trying to pass a date as a parameter to the command, but it is always evaluated as today's date. If I print the parameter in the report header, it is correct, but if I select it from the database it is incorrect.

select ( select to_char ( {?Pm-@ReportMonthDate}, 'DD/MM/YYYY' ) from dual ) xxx,
( select nvl(value_nbr,0)
from arg_mnl_data_rcrd mdr,
arg_mnl_data_elmnt mde
where mdr.rlvnt_dt = {?Pm-@ReportMonthDate}
and mdr.data_elmnt_id = mde.data_elmnt_id
and mde.cr_key = 'DUTY_DAYS_AIRCRAFT' ) DUTY_DAYS_AIRCRAFT
FROM DUAL;

Werner

 

[hilfy]

This is more of a database question than a Crystal question. You don't mention what database you're using, but I'm going to assume it's Oracle because of your use of "Dual". In Oracle, you always take a performance hit when you use Dual in your selection, so you should avoid it if possible. Try this:

select to_char ({?Pm-@ReportMonthDate}, 'DD/MM/YYYY' ) PMDate,
      nvl(value_nbr,0) 
 from arg_mnl_data_rcrd mdr,
      arg_mnl_data_elmnt mde
where mdr.rlvnt_dt = {?Pm-@ReportMonthDate}
  and mdr.data_elmnt_id = mde.data_elmnt_id
  and mde.cr_key = 'DUTY_DAYS_AIRCRAFT' ) DUTY_DAYS_AIRCRAFT

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[wreiche]

I am using Oracle 9.2. The problem is not with the oracle query but with what crystal does with the parameter.

I tried the change you suggested (removing the trailing ") DUTY_DAYS_AIRCRAFT" from my original query and get no rows returned (because the "where mdr.rlvnt_dt = {?Pm-@ReportMonthDate}" uses the current date, not the date in which should be stored in this parameter.

The simplest query that I can use to demonstrate the problem is as follows:

select to_char ({?Pm-@ReportMonthDate}, 'DD/MM/YYYY' ) PMDate from dual

{?Pm-@ReportMonthDate} is set to the current date, yet
PMDate is set to today's date.

It is almost as though the SQL commands are executed before the parameters are supplied by the user.

 

[wreiche]

I've identified the problem but haven't got a solution. I've used the same parameter name in the main report, the sub report and in the query within the subreport. CR is smart enough to determine that the parameter should only need to be entered once, but within the query it does not use that value - it uses the unitialised query parameter value.