Report writer query help

From: Diane Johnston <tvordlj_at_canada.com>
Date: Wed, 18 Jul 2001 14:00:37 GMT
Message-ID: <3B559600.557F8419_at_canada.com>


[Quoted] I've tried this in sql plus and it seems to grab what i want but in Developer Reports 6i in a query it returns nothing. I'm counting the number of records for a certain time of day but if the time of day goes past midnight, i need to count those that have a time of day before 05:00 the next day. An input parameter, :timeofday, contains the time ranges requested, with one being 18:00-05:00 .. that's the value that is being substringed in the query below. I want it to check the parameter and if the string = "05:00" then return the record with date + 1 day concantenated with the "05:00" otherwise, return the current record plus the concatenated "05:00".

select tsa_rte, count(*) ttl_ontime_w from tsa_timerec
[Quoted] where to_char(tsa_dep_dte,'HH24:MI') >= SUBSTR(:timeofday,1,5)

     and to_char(tsa_dep_dte,'DD-MON-YYYYHH24:MI') <=      
DECODE(substr(:timeofday,7,5),'05:00',to_char(tsa_dep_dte+1,'DD-MON-YYYY')||SUBSTR(:timeofday,7,5),                                           
[Quoted] to_char(tsa_dep_dte,'DD-MON-YYYY')||SUBSTR(:timeofday,7,5))   GROUP BY tsa_rte;

Suggestions?

-- 
Diane Johnston 
It's not a smile, it's a lid on a scream!
Received on Wed Jul 18 2001 - 16:00:37 CEST

Original text of this message