Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using parameters in a command query

Status
Not open for further replies.

wreiche

IS-IT--Management
Sep 28, 2006
36
CA
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
 
By saying:
wreiche said:
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?







[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top