Hmm,
my_limit_date DATE:= trunc(sysdate) - 6; -- !!! only 6
erh.arrival_dt < my_limit_date
JP
On Thu 15. November 2001 16:00, you wrote:
> I think that the following statement is keeping this application from using
> an index in my cost-based execution plan. This is an 8.0.4 database
cc: [EMAIL PROTECTED]
Subject: Re: Need help rewriting SQL with
TRUNC function
11/15/01
l piece of information.
>
> Cherie
>
>
>
> Jan Pruner
> <[EMAIL PROTECTED] To: [EMAIL PROTECTED]
>
> >cc: [EMAIL PROTECTED]
>
> S
>
>I think that the following statement is keeping this application from using
>an index in my cost-based execution plan. This is an 8.0.4 database so I
>don't have the option of creating a function-based index.
>
>I'm not that great with SQL. Can anyone help me rewrite this statement so
>that I
(trunc(sysdate) - trunc(erh.arrival_dt)) >= 7
becomes
- trunc(erh.arrival_dt) >= 7 - trunc(sysdate)
becomes by negating
trunc(erh.arrival_dt) <= trunc(sysdate) - 7
becomes
erh.arrival_dt <= trunc(sysdate) - 7 + 0.9
hth
connor
--- [EMAIL PROTECTED] wrote: >
> I think that the followi
Cherie,
Change the clause as follows
orig ==> (trunc(sysdate) - trunc(erh.arrival_dt)) >= 7 AND
new ==> erh.arrival_dt <= sysdate - 7 AND
Hope this helps!
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, November 15, 2001 10:00 AM
To: Multiple
Wouldn't this introduce a time-of-day dependency on the result?
Instead, how about:
(trunc(sysdate) - erh.arrival_dt)) > 6 AND
(ie. strictly greater than six days ago)
- Bill.
At 08:30 15/11/01 -0800, you wrote:
>Cherie,
>
>Change the clause as follows
>
>orig ==> (trunc(sysdate) - trunc(e