Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59

    Unanswered: Nest Case Expression

    Hi everybody,

    Does someone know how can I nest "case" expressions?
    I know that to avoid exceeding the limit of 128 choices ( this is a limitation of case), I should nest CASE expressions.
    I have more than 300 pair of conditions to analyze inside a table column.....
    I am doing like this: before I complete the 128 choices, I repeat the case expression....
    PS: Column in my example is a varchar(2) datatype
    Example:
    SELECT CASE column
    WHEN 'x' THEN 'XXX'
    WHEN 'Y' THEN 'YYY'
    ...
    ...
    ...
    ...
    CASE column
    WHEN 'a' THEN 'AAA'
    WHEN 'b' THEN 'BBB'
    ...
    ...
    ...
    ...
    ELSE 'ZZZ' END
    FROM TABLE

    Please send me your corrections to the query above...
    Nadia

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My initial thought would be to breakup the collection into thirds by using a couple of IF...THEN...ELSE statements; assuming each 1/3 has less than 128 possibilities.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes you can nest them, for example...

    PHP Code:
    select somecolumn,
           case 
    when somecolumn like 'FRA/0001%' then 
                
    case when somecolumn like 'FRA/00011%' then 'its a 11' 
                     
    when somecolumn like 'FRA/00012%' then 'its a 12'
                     
    when somecolumn like 'FRA/00013%' then 'its a 13'
                     
    when somecolumn like 'FRA/00014%' then 'its a 14'
                     
    when somecolumn like 'FRA/00015%' then 'its a 15'
                     
    when somecolumn like 'FRA/00016%' then 'its a 16'
                     
    when somecolumn like 'FRA/00017%' then 'its a 17'
                     
    when somecolumn like 'FRA/00018%' then 'its a 18'
                     
    when somecolumn like 'FRA/00019%' then 'its a 19'
                
    end
           end
    from sometable
    where somecolumn like 
    'FRA/0001%' 
    You will have to determine how to best nest those expressions for your data though :-)

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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