Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unanswered: Using CASE within WHERE clause

    Dear All,

    i was wondering if I can use CASE within WHERE clause in STATIC SQL. I am creating a stored procedure which will receive a parameter parm=...

    If parm is equal 'ABC' then WHERE clause should be:

    WHERE
    ...... AND source = 'XYZ'

    otherwise

    WHERE
    ...... AND source <> 'XYZ'

    Your help would be greatly appreciated

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... if I can use CASE within WHERE clause in STATIC SQL.
    Yes!
    You can use CASE expressions in WHERE clause.
    (I couldn't undersand why you doubt about using CASE expressions in WHERE clause.)


    If parm is equal 'ABC' then WHERE clause should be:

    WHERE
    ...... AND source = 'XYZ'

    otherwise

    WHERE
    ...... AND source <> 'XYZ'
    It might be unnecessary to use CASE expression in the condition.

    Note: I assumed parm would be not null.
    Example 1:
    Code:
     WHERE ...
      AND
      (    parm =  'ABC' AND source =  'XYZ'
       OR  parm <> 'ABC' AND source <> 'XYZ'
      )
    or

    Example 2:
    Code:
     WHERE ...
      AND
      (    parm = 'ABC' AND source = 'XYZ'
       OR
       NOT(parm = 'ABC' OR  source = 'XYZ')
      )

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tonkuma's got it, plus depending on the version of DB2, the optimizer might prune the false condition prior to execution, eliminating the AND/OR condition.

  4. #4
    Join Date
    Jan 2014
    Posts
    3
    Thank you very much. Will try it and let you know

  5. #5
    Join Date
    Jan 2014
    Posts
    3
    Thank you very much. It works like a charm

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    EugeneE
    One thing I forgot way back when this was answered for you. A case statement can be used within the WHERE clause, just not in the way you worded it above. It would look something like:
    Code:
    select some_cols
       from some_tables
    where something = something_else
      and (case when condition = ??
                          then this_value
                     else my_other_value
              end) = this_col
    You can have AND conditions within the WHEN clause, cases within the case etc... even a (case) = (case)
    Dave

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by EugeneE View Post
    a stored procedure which will receive a parameter parm=...
    So this is not a SQL-only casus. SQL/PL also plays a role. You might code 2 SELECT statements which are executed conditionally (based upon the value of the parameter).

    • as a programmer you would rather not because you want 1 single point of definition and sources easy to maintain.

    • as a dba it would be the ideal situation because you would always fire the optimized SQL (plain & simple WHERE clause specifying exactly what you want)

    Like always: it depends and you'll have to make choices. It is safe to go for the SQL solution as opted before and keep the 2nd option as a card in your sleeve so you know what you can do whenever you must work on performance.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •