Syntax/Logic Trouble in Where Clause

[kramerd1506]

Hi folks, you're all so helpful that I just had to come back.

Here's the code I'm working with:

select distinct
....
from
table a
table b
(select to_char((sysdate), 'dy') from dual) dt
where
....
AND
if dt = 'fri'
then

((a.date1 >= trunc(sysdate+1)
and a.date1 < trunc(sysdate+4))
OR
(b.date2 >= trunc(sysdate+1)
AND b.date2 < trunc(sysdate+4)))

else

((a.date1 >= trunc(sysdate+1)
and a.date1 < trunc(sysdate+2))
OR
(b.date2>= trunc(sysdate+1)
AND b.date2 < trunc(sysdate+2)))

AND
....

When I run this I get an error at "if dt = 'fri'" of Invalid Relational Operator.

Any advice? Thanks in advance, as always.

 

[SantaMufasa]

Kramer,

In Oracle SQL, we cannot use "IF" as a relational invocation; we must stay within the "WHERE...AND...OR..." convention. Also, as a code optimisation, we can use the "BETWEEN" operator instead of the construct you have used. (Note: the BETWEEN operator includes the two endpoints, but since you have used TRUNC(SYSDATE+...), you will still get the effect you wish). Here is the code rewrite that should work for you:

select distinct
....
from
table a
table b
(select to_char((sysdate), 'dy') from dual) dt
where
....
AND (  (dt = 'fri' AND
        (a.date1 BETWEEN trunc(sysdate+1) AND trunc(sysdate+4))
        OR
        (b.date2 BETWEEN trunc(sysdate+1) AND trunc(sysdate+4))
       )
OR  (   a.date1 BETWEEN trunc(sysdate+1) AND trunc(sysdate+2)
        OR 
        b.date2 BETWEEN trunc(sysdate+1) AND trunc(sysdate+2)
    )
    )
AND ....

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.