Oracle SQL - CASE in a WHERE clause

  • 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

  • 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.

    Thanks for the answer. This works. I am sorry that my question was not clear.

    Thanks for the answer!.

  • 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%')
    

    Thanks for the answer. This works too. I am sorry that my question was not clear. I am sorry, but I can only mark one answer as "Accepted Answer"

  • 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.

    Thanks. I was more or less looking as to how I should do this. I din't care for the result. I was just looking for a set of code that would not give me an error. I will though take a second look and put an answer out.

    If the answer is anything that removes the error than the solution set is infinite and easiest solution is to remove the WHERE clause, which obviously wouldn't be helpful. So that this answer will be helpful in the future I suggest you change it to something like "How can CASE be referenced in a WHERE clause", which would fit with your accepted answer.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM