Placing a CASE statement in a WHERE Clause

[LeonelSanchezJr]

I need to apply certain filters to my WHERE clause
depending on the certain conditions which I believe can be met by a CASE statement.

example:

SELECT *
FROM TABLE
WHERE TABLE.COUNTRY = 'ABC'

(CASE WHEN TABLE.INVTYPE = '999'
THEN AND TABLE.ARTYPE <> '555'
END
)
;

The error I get is:
SQL Command not properly ended.

Thanks,

Leo ;-)

 

[SantaMufasa]

Leo,

Perhaps I'm missing something, but can you not simply say:

SELECT *
FROM TABLE
WHERE TABLE.COUNTRY = 'ABC'       
  AND TABLE.INVTYPE = '999'
  AND TABLE.ARTYPE <> '555';

We use the CASE construct as a more intuitive DECODE function. In your example, the CASE clause is syntactically incomplete.

Here is a contrived example of a CASE clause in a WHERE statement (to illustrate working code):

select last_name, salary from s_emp
where CASE WHEN salary > 1500 then 'A'
           WHEN salary < 1000 then 'B'
      END in ('A','B');

LAST_NAME                     SALARY
------------------------- ----------
Velasquez                     3327.5
Ropeburn                        1550
Sedeghi                         1515
Nguyen                          1525
Smith                            940
Patel                            795
Newman                           750
Chang                            800
Patel                            795
Dancs                            860

10 rows selected.

Let us know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

The WHERE clause is already a conditional clause, Why embed another conditional which could be satisfieds with logical operators instead of a CASE statement (even if it's allowed)?

Besides, the CASE clause you posted is misconstrued, to be valid, you would need something like this:

SELECT *
FROM TABLE
WHERE TABLE.COUNTRY = 'ABC'       
 [red]AND SomeColumn = [/red]
(CASE WHEN TABLE.INVTYPE = '999'
       AND TABLE.ARTYPE <> '555'
 THEN [red]'SOME RESULT'
 ELSE 'What?'[/red]
 END);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LeonelSanchezJr]

This situation is quite strange because I need to apply 2 different rules.

When the INVTYPE = '999'
then, I DO NOT want to include ARTYPEs of '555'
Otherwise, I do want to indluce ARTYPES of '555'


Thanks,

Leo ;-)

 

[BJCooperIT]

when (INVTYPE != '999')
or   (INVTYPE = '999' and ARTYPE != '555')

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

I still believe that you do not need a CASE statement...just say:

...WHERE (invtype  = '999' AND artype != '555')
      OR (invtype != '999' AND artype  = '555')

Right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LeonelSanchezJr]

okay thank you; I appreciate it

Thanks,

Leo ;-)

 

[BJCooperIT]

of course I meant WHERE...

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

WHEN"..."WHERE"...Besides Oracle/SQL, BJ, who really cares?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]