Using parameters in a command query

[wreiche]

I am trying to generate a report which extracts data from an Oracle database using a database command which has two parameters - the start and end times of the data to be extracted. The query works if the date/time selection criteria is removed, but returns all rows. The query also works if it is run from Sql*Plus. It does not return any rows if it is run within crystal reports. Any ideas?

select subtype image_type,
avg (cat_date - valid_time ) * 24 * 60 avg_proc_tm,
count(*) image_cnt
from oobjectmd
where object_type = 'IM'
and (cat_date - valid_time ) > 0
and (cat_date - valid_time ) < 1
and valid_time >= to_date ( {?StartDate}, 'dd-mm-yyyy' )
and valid_time <= to_date ( {?EndDate}, 'dd-mm-yyyy' )
group by subtype
order by subtype

 

[Turkbear]

By saying:

using a database command

Do you mean you are using a Command Object?
If so, did you define the Date parameters in the Command Object? Are they defined as Strings?









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[wreiche]

Thanks Turkbear. The problem was that the parameters were defined as 'date' and should not have the to_date function applied to them. The query now works:

select subtype image_type,
avg (cat_date - valid_time ) * 24 * 60 avg_proc_tm,
count(*) image_cnt
from oobjectmd
where object_type = 'IM'
and (cat_date - valid_time ) > 0
and (cat_date - valid_time ) < 1
and valid_time >= {?StartDate}
and valid_time <= {?EndDate}
group by subtype
order by subtype