2 Replies Latest reply: Feb 10, 2011 7:26 PM by Frank Kulash RSS

    Case statement in a where clause

    27807
      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.ACCURAL_TYPE
      WHEN 'A'
      THEN T1.COST_TYPE IN('NOTHING')
      ELSE T1.COST_TYPE NOT IN('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
        • 1. Re: Case statement in a where clause
          JustinCave
          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.

          Justin
          • 2. Re: Case statement in a where clause
            Frank Kulash
            Hi,

            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:
            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'))
            ;
            It's not wrong to use CASE in a WHERE clause; just be sure you consider the alternatives.

            Edited by: Frank Kulash on Feb 10, 2011 8:23 PM