Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why aren't these queries the same?

Re: Why aren't these queries the same?

From: Ed Stevens <Ed_Stevens_at_nospam.noway.nohow>
Date: Thu, 07 Mar 2002 15:29:21 GMT
Message-ID: <3c8785d4.268202364@ausnews.austin.ibm.com>


Nope, that was the first thing I checked., but in the mean time I did figure out the problem.

The date math in the original (with the TRUNC function) was being applied to just the "days" of the date field, so it would catch anything from 12 days ago. Without the TRUNC function, it would catch anything from 12 days ago down to the time the query was executed.

To clarify that, let's assume the interval was 1 day. In the original, it would select anything with a date of yesterday. In the new query it would select anything more than 24 hours old -- close but not quite the same thing.

As for the >= vs. <=, when using one of these constructs, if you reverse the comparison you have to drop the '='. Set yourself up a small example on paper and try it.

On Wed, 6 Mar 2002 16:03:06 -0800, "santosh sharma" <santysharma_at_yahoo.com> wrote:

>I think,
>where mod_date < (sysdate - 12)
>should be
>where mod_date <= (sysdate - 12)
>
>"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message
>news:3c86991a.207611960_at_ausnews.austin.ibm.com...
>> I'm going through a process of identifying our biggest I/O hogs and
>working with
>> the devleopers to fix them. One statement has us stumped. The original
>looks
>> like this
>>
>> select count(*)
>> from scott.table_a
>> where (trunc(sysdate) - trunc(scott.table_a.mod_date) >= 12)
>> /
>>
>> so what they are looking for are instances of mod_date that are 12 or more
>days
>> old.
>>
>>
>> We re-wrote the query to look like this
>>
>> select count(*)
>> from scott.table_a
>> where mod_date < (sysdate - 12)
>> /
>>
>> but they don't return the same results.
>>
>> I did confirm that trunc(sysdate) returned the same value as sysdate and
>> trunc(scott.table_a.mod_date) returned the same value as
>scott.table_a.mod_date.
>>
>>
>> --
>> Ed Stevens
>> (Opinions expressed do not necessarily represent those of my employer.)
>
>

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)
Received on Thu Mar 07 2002 - 09:29:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US