This content has been marked as final. Show 2 replies
Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.
I'm guessing that you want something like
But I'm making a lot of guesses about what your code is supposed to mean.
SQL> ed Wrote file afiedt.buf 1 WITH T1 AS 2 ( 3 SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL 4 SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL 5 SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL 6 SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL 7 SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL 8 select 6 as seq, 'SOMETHING 4' AS SOME_type from dual 9 ) 10 , T2 AS 11 ( 12 SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL 13 SELECT 'B' AS COMPARE_type FROM DUAL 14 ) 15 SELECT T2.*, T1.* 16 FROM T1, T2 17 WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND 18 T1.SOME_TYPE LIKE 'NOTHING%' 19 THEN 1 20 WHEN T2.COMPARE_TYPE != 'A' AND 21 T1.SOME_TYPE NOT LIKE 'NOTHING%' 22 THEN 1 23 ELSE 0 24* END) = 1 SQL> / C SEQ SOME_TYPE - ---------- ----------- A 1 NOTHING 1 A 2 NOTHING 2 B 3 SOMETHING 1 B 4 SOMETHING 2 B 5 SOMETHING 3 B 6 SOMETHING 4 6 rows selected.
CASE expressions are wonderful, because they let you put conditional logic anywhere, such as SELECT- and ORDER BY clauses.
The WHERE clause allows conditions anyway, so CASE expressions aren't nearly as helpful there. For example:
It's not wrong to use CASE in a WHERE clause; just be sure you consider the alternatives.
SELECT t2.*, t1.* FROM t1, t2 WHERE (t2.compare_type = 'A' AND t1.some_type IN ('NOTHING')) OR (NVL ( t2.compare_type , 'B' ) != 'A' AND t1.some_type NOT IN ('NOTHING')) ;
Edited by: Frank Kulash on Feb 10, 2011 8:23 PM