CASE Statement in a Where Clause Problem

[cbochner]

Hi,

I'm trying to write the following SQL code with lots of problems. Please Help...

Select * from

where
CASE WHEN dte.weekday_no = 2 THEN
dre.l_mod_datetime >= to_char(today-4,"%Y-%m-%d") || ' 18:00'
ELSE
dre.l_mod_datetime >= to_char(today-2,"%Y-%m-%d") || ' 18:00'
END

My criteria should depend on whether today is Monday or not.

Thanks Alot.

Chaim Bochner

 

[gmmastros]

Just guessing here, but try this...

Select * from [table]
where l_mod_datetime >= 
CASE WHEN dte.weekday_no = 2 THEN
        to_char(today-4,"%Y-%m-%d") || ' 18:00'
        ELSE
        to_char(today-2,"%Y-%m-%d") || ' 18:00'
END

-George

"the screen with the little boxes in the window." - Moron

 

[cbochner]

dre.l_mod_datetime >= to_char(today-4,"%Y-%m-%d") || ' 18:00' is a conditional statement not an expression.

igamine...

Where x = y
and dre.l_mod_datetime >= to_char(today-4,"%Y-%m-%d") || ' 18:00'

instead...

CASE WHEN x = y THEN
and dre.l_mod_datetime >= to_char(today-4,"%Y-%m-%d") || ' 18:00'
ELSE
and dre.l_mod_datetime >= to_char(today-2,"%Y-%m-%d") || ' 18:00'
END
and...
order by...

Please Help.

Chaim

 

[PHV]

is a conditional statement not an expression
But CASE ... WHEN returns an expression and therefore just follow Georges's suggestion ...

 

[cbochner]

So how do I setup a conditional statement in a where clause in SQL? How do I say: if today is monday query x..., if today is not monday y...?

Thanks,

Chaim

 

[PHV]

Seems you didn't make sense with George's code ...
George's way:
WHERE dre.l_mod_datetime >= CASE WHEN dte.weekday_no = 2
THEN to_char(today-4,"%Y-%m-%d") || ' 18:00'
ELSE to_char(today-2,"%Y-%m-%d") || ' 18:00'
END

My suggestion:
WHERE dre.l_mod_datetime >= to_char(today-CASE WHEN dte.weekday_no = 2 THEN 4 ELSE 2 END,"%Y-%m-%d") || ' 18:00'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cbochner]

Thanks guys for all your help. It worked just fine...

Chaim