| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with Where clause
A copy of this was sent to Jeff McClure <jmcab1_at_ameritech.net>
(if that email address didn't require changing)
On Fri, 02 Oct 1998 18:06:01 -0500, you wrote:
>Folks,
>
>I have a problem in a query. Who doesn't? This one is driving me
>nuts. The problem appears to be in the where clause.I am attempting to
>obtain records whose timestamp is within a time minute window spanning
>the current time to the current time - 10 minutes (a ten minute moving
>window). This SQL will run from cron every ten minutes and be used to
>monitor an event. In my where clause, I specified
>1)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') <=
>to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and
>to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
>.006944444,'MM/DD/YYYY HH24:MI:SS')
>
>.006944444 is the fractional (day) equivelant of 10 minutes...
>
>2)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') between
>to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and
>to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
>.006944444,'MM/DD/YYYY HH24:MI:SS')
>
>Both seem to return records within the ten minute timeframe
>corresponding to when the statement was run. However, both also return
>records whose date (day) is different.
well, to work around that you could use YYYY/MM/DD HH24:MI:SS (a date string that will 'sort' in ascii according to the year/month/day/hour/minute/second) or more simply just use DATE comparisions in the first place...
Instead of coding:
>where
>to_char(b.start_date, 'MM/DD/YY HH24:MI:SS') between to_char(sysdate -
>.006944444,'MM/DD/YY HH24:MI:SS') and to_char(sysdate,'MM/DD/YY
>HH24:MI:SS')
try:
where b.start_date between sysdate-1/24/60*10 and sysdate
QED -- and it would let you use an index on start_date if available.... (using the to_char(start_date) nullifies any indexes on start_date...)
>For example, if I were to run this statement now (10/02/1998 17:30:00)
>I would recieve records back with the timestamp of 09/25/1998 17:26:07,
>etc.
>
>Am I missing something here? What am I doing wrong (except attempting
>to write SQL? I'm obviously not a programmer type by trade!!!, but I
>can write some pretty slick SQL) Is there a better way of doing this?
>
>Heres my SQL:
>
>clear buffer
>clear columns
>set echo off
>set feedback off
>set verify off
>set heading off
>set linesize 132
>set pagesize 60
>
>column col_start FORMAT A17
>column col_chnum FORMAT 099
>column col_logname FORMAT A50
>column col_mins FORMAT 99999
>column phy_addr FORMAT A20
>column stat1 FORMAT A13
>column stats FORMAT A13
>
>spool NVOD_Sch_cron.out
>
>select
> h.channel_number col_chnum,
>
>substr(physical_network_address,5,instr(physical_network_address,'_',1,1)
>- 5) ||':'||
>substr(physical_network_address,instr(physical_network_address,':',1,2)
>+ 1, 5) phy_addr,
> a.status stat1,
> b.status stats,
> to_char(b.start_date, 'MM/DD/YY HH24:MI:SS') col_start,
> (sum(g.milliseconds)/1000)/60 col_mins,
> substr(d.name,1,instr(d.name,'(',1,1) - 1) col_logname
>from nvod a, bi_schedules b, lcontent_track c, logical_content d,
>track_clip e, clips f, content g, bi_channels h
>where
>to_char(b.start_date, 'MM/DD/YY HH24:MI:SS') between to_char(sysdate -
>.006944444,'MM/DD/YY HH24:MI:SS') and to_char(sysdate,'MM/DD/YY
>HH24:MI:SS')
>and (b.id = a.schedule_id)
>and (d.id = a.logical_content_id)
>and (c.logical_content_id = a.logical_content_id)
>and (e.track_id = c.track_id)
>and (f.id = e.clip_id)
>and (g.id = f.content_id)
>and (h.id = a.channel_id)
>group by
>h.channel_number,h.physical_network_address,a.status,b.status,b.start_date,b.stop_date,d.name
>order by
>h.channel_number,h.physical_network_address,a.status,b.status,b.start_date,b.stop_date,d.name
>;
>
>spool off
>
>
>Any input would be greatly appreciated as I am on an extremely tight
>timeline, and this SQL could be one of a very few factors that affect a
>go/no-go decision on this project.
>
>Thanks in advance,
>
>Jeff McClure
>Specialist Database Analyst
>Ameritech New Media
>e-mail: Work: Jeff.W.McClure_at_Ameritech.com
> Home: jmcclure_at_mcs.com
> jmcab1_at_Ameritech.net
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Oct 02 1998 - 19:15:57 CDT
 |
 |