DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: CASE in a WHERE clause

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    CASE in a WHERE clause

    Is it possible to use CASE in a WHERE clause? If yes hpw would the syntax look like.

    Thanks

    Roman

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Yes you can use "Case" statement in Where clause.

    Syntax could look something like :

    select * from test_table
    where (case when column_a = 'A' then 'B' else 'C') = ;


    HTH.
    -- Dilip

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    I get an error when I try something like this? Any ideas?

    WHERE
    (CASE
    WHEN d.carrier in ('AWS Test', 'AWS Lab') THEN 'AWS'
    WHEN d.carrier = 'Bell Mobility - DataTac' THEN 'Bell Mobility - DataTac'
    WHEN d.carrier IS NULL OR d.carrier in ('Unknown', 'Unknown Carrier') THEN 'Unknown Carrier'
    ELSE d.carrier
    END)
    and c.dte between to_date ('01-Dec-2003') and to_date('01-Dec-2003')
    and c.pin=d.pin(+)

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The case statement is not a predicate in itself -- you need to compare it to something , like ...

    Code:
    WHERE
    (CASE
    WHEN d.carrier in ('AWS Test', 'AWS Lab') THEN 'AWS'
    WHEN d.carrier = 'Bell Mobility - DataTac' THEN 'Bell Mobility - DataTac'
    WHEN d.carrier IS NULL OR d.carrier in ('Unknown', 'Unknown Carrier') THEN 'Unknown Carrier'
    ELSE d.carrier
    END) = 'whatever'
    This may not be the most efficient method though -- you'll not get any index-based access to d.carrier
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Originally posted by patel_dil
    select * from test_table
    where (case when column_a = 'A' then 'B' else 'C') = ;


    HTH.
    Above syntax was infact,

    select * from test_table
    where (case when column_a = 'A' then 'B' else 'C') = 'some value' ;

    html did not like "<" and ">".

    Like slimdave said, the case statement will just return some value which you can use in predicate.

    Thanks
    -- Dilip

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    This may not be the most efficient method though
    TRUE, but my concern would be that the CASE structure adds a data transformation that might make the logic less easy to follow and maintain; it can be easly rewritten without the CASE (perhaps with better performance). Unless the case structure is a copy/paste job from elsewhere, purely to enhance maintainability . . . .

Posting Permissions

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


Click Here to Expand Forum to Full Width