sql - How to only apply condition in WHERE if condition is met-CodePudding

I'm having trouble implementing this, where I want to have the condition in WHERE removed/added if a condition is met.

sample:

select x.color, x.shape
from table x
x.finish = 'shine'

What I want this to do is that if x.color is not, for example, 'Red', then x.finish = 'shine' in WHERE should not apply.

TIA!

CodePudding user response：

Use OR to match the conditions when color is not equal to 'Red' (which is when it is either not equal to 'Red' or when it is NULL):

SELECT color,
       shape
FROM   table_name
WHERE  color != 'Red'
OR     color IS NULL
OR     finish = 'shine'

CodePudding user response：

You can implement conditions within Where clause by using CASE. There can be multiple when - then parts. It is executed sequentialy and the first satisfied WHEN condition will return the according THEN value end exit CASE. Regards...

SELECT 
    t.COLOR, 
    t.SHAPE
FROM   
    TAB_NAME t
WHERE  
    t.FINISH = CASE 
                 WHEN Nvl(t.COLOR, 'Red') != 'Red' THEN 'Shine'
             ELSE 
                 t.FINISH 
             END