Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why aren't these queries the same?
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