Case statement in where clause - possible?

[DRH192]

Hello All,

I am writing a business objects report in freehand SQL that connects to an Oracle data warehouse.

I need the report to refresh every weekday. On Tuesday I need it to include data for fri sat and sun. On Mon Wed Thur and Fri I need the report to look for data from 2 days ago.

I can use sysdate-2 for the Mon Wed Thur and Fri scenarios. But how do I put an If or Case clause into the SQL to decide if the day is a Tuesday?

I'm pretty much a begginner so please explain any script!

Many Thanks

Duncan

 

[Dagon]

Yes, CASE can be used in WHERE conditions e.g.

select * from (select trunc(sysdate) as sample_date from dual)
where sample_date = (case when to_char(sysdate, 'DY') = 'WED' then trunc(sysdate) else trunc(sysdate)-2 end)

 

[SantaMufasa]

...And to add to Dagon's correct observation, an Oracle CASE statement can appear anywhere in an SQL statement that any data expression can appear (since, in the final analysis, a CASE statement evaluates to a simple data expression).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Quick (minor) point. This is a CASE expression a CASE statement is slightly different construct (which is NOT available to SQL, only PL/SQL)

 

[SantaMufasa]

Good catch, Jim...I shooda thunka that myself.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

It's a wee bit picky, but might be significant when searching for help on it (just the sort of thing that can be really frustrating to a newbie), so I thought I'd mention it

 

[DRH192]

Thanks for all the helpful advice people, I was surprised to see this many replies in one day! I am about to have another crack at my report now, probably be back with some more questions.

cheers

 

[sem]

The issue is that to_char(sysdate, 'DY') = 'WED' statement is not NLS-agnostic, be careful if you work for an international customer.

Regards, Dima

http://seminihin.narod.ru