Oracle SQL - CASE in a WHERE clause

Question

Is it possible to somehow do this?

WITH T1 AS
(
   SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
   select 6 as seq, 'SOMETHING 4' AS SOME_TYPE from dual
)
, T2 AS
(
   SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
   SELECT 'B' AS COMPARE_TYPE FROM DUAL 
)
SELECT T2.*, T1.* 
FROM T1, T2
WHERE  CASE T2.COMPARE_TYPE 
         WHEN 'A'
            THEN T1.SOME_TYPE LIKE 'NOTHING%'
         ELSE T1.SOME_TYPE NOT LIKE 'NOTHING%' 
      END

I know that my WHERE is clause is not correct.

Any help would be great in knowing if this type of statement is possible.

I don't want to write a Dynamic SQL. If I have to I will write 2 different SQL statements.

Thanks

Answer 1

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

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.

But I'm making a lot of guesses about what your code is supposed to mean.

That this appears to be identical to a question someone asked in the OTN forums. My answer is the same in both places.

Answer 2

Try writing the where clause this way:

WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
   OR (T2.COMPARE_TYPE <> 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')

Answer 3

Justin Cave and Eric Humphrey's queries both return different results. Here is a third equally valid answer that returns a third different set of results:

WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
OR T1.SOME_TYPE NOT LIKE 'NOTHING%' 

Only you will know which answer gives the results you are expecting, but they are all correct answers to the question as given.

Your question is better than many because it is self contained and includes the source data, but it will help if you include the output you are looking for as well. I recommend you add that to the question and make sure the accepted answer matches those results.