I am trying to use NESTED CASE statement(BOLD Below) where, based on first CASE statment, I need to verify inner where clause, else other inner where clause. Is below right approach when using nested CASE When statements?
SELECT COUNT(*)
FROM <list of table>
WHERE <list of conditions >
AND CASE WHEN cond1 <> 1 or cond2 <>2 or cond3 <> 3
THEN
WHEN ( col1,col2,col3) NOT IN (SELECT col1,col2,col3
FROM table 1,table 2
WHERE <condition1> )
ELSE ( col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
FROM table 1,table 2
WHERE <condition2> )
END
Use AND and OR:
SELECT COUNT(*)
FROM <list of table>
WHERE <list of conditions >
AND ( ( ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
AND (col1,col2,col3) NOT IN (SELECT col1,col2,col3
FROM table1
INNER JOIN table2
ON (<condition>)
WHERE <condition1>)
)
OR ( NOT ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
AND (col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
FROM table1
INNER JOIN table2
ON (<condition2>)
WHERE <condition2> )
)
)
Notes: Do not use legacy comma joins, use the modern ANSI join syntax.