RE: sysdate - 90 mins query

why do you need union? it looks like a straightforward query to me, you are only using one table

select * from table
WHERE TO_DATE('TM_DATE' || ' TM_TIME','YYYYMMDD') = to_char(sysdate - (90/1440),'mmddyy hh24:Mi:ss')
AND ROWNUM < 5

At least I'm able to do something similar at the command line

  1 SELECT to_char(sysdate,'mmddyy hh24:Mi:ss') current_time,   2 to_char(sysdate - (90/1440),'mmddyy hh24:Mi:ss') less_90_min   3* FROM DUAL
SQL> / CURRENT_TIME LESS_90_MIN
--------------- ---------------
050402 10:48:38 050402 09:18:38

SQL> The reason dba's in general don't like unions is because they cause sorts in the background, it's performing a select distinct on the result set. Use UNION ALL whenever possible. I suggest you read up on SQL, union or union all isn't needed in this query

Lisa Koivu
Oracle Database Monkey Mama
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063

> -----Original Message-----
> From: sarath kumar [SMTP:sarath_kumar0_at_yahoo.com]
> Sent: Saturday, May 04, 2002 10:38 AM
> To: Multiple recipients of list ORACLE-L
> Subject: sysdate - 90 mins query
>
> i hava a table tbl_tmstmp with date and time as 2
> different columns (tm_date number)and tm_time
> varchar2(6).
> select * from tbl_tmstmp;
> tmstmp_id tm_date tm_time
> -------- -------- -------
> 1 20020502 12:30
> 2 20020503 19:30
> 3 20020503 23:30
> 4 20020504 00:30
> 5 20020504 02:30
> 6 20020504 08:30
>
> i have requuirement to pickup the records which are
> sysdate - 90 minutes. Assuming the sysdate is 20020504
> and time is 03:00 AM i need to pick up the first 4
> records.
>
> is it possible without using the UNION as DBAS are
> picky about using UNIONS.
>
> thanks
> Srini.
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - your guide to health and wellness
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: sarath kumar
> INET: sarath_kumar0_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).