CASE clause in the WHERE section 1

[dancarr22]

Anyone know how to put a CASE or IF statement in a where clause...this didn't work
SELECT NAME FROM CUSTOMERS WHERE
CASE WHEN SYSDATE=SYSDATE THEN
NAME LIKE 'a%'
ELSE
NAME LIKE 'b%'
END

 

[swampBoogie]

What are you trying to do?

Why SYSDATE=SYSDATE ?

A case expression returns a value, it can not be used as a macro.

 

[dancarr22]

That was just a test to see if the logic works.
I want to change the WHERE clause depending on
a given value. For example if today is a Monday
I want to pull up all the 'Monday' clients
so WHERE Today='Monday' if today is Tuesday
I want WHERE Today='Tuesday'. It may sound
a bit screwy but it's what we need because
we can't save functions, etc. to the database
and need to do everything in in-line SQL

 

[swampBoogie]

To continue your example, it could look like

SELECT NAME
FROM CUSTOMERS
WHERE name like
CASE WHEN SYSDATE=SYSDATE THEN
'a%'
ELSE
'b%'
END

Do you really need a case expression in your case, e.g. something like

select * from customer where lower(today) = lower(to_char(to_date(sysdate,'DAY'))

With reservation for total misunderstanding and forgotten Oracle skills.

 

[ChrisHunt]

You can use a DECODE() statement thus:

SELECT NAME 
FROM CUSTOMERS 
WHERE name LIKE DECODE(SYSDATE,SYSDATE,'a%','b%')

Whether it makes sense to do so is another matter...
-- Chris Hunt
Extra Connections Ltd

http://www.extracon.com