to nest cases or not?

  • Im trying to run this query but it really doesn't like it ... (I always post on a friday) something to do with the hangover affecting my brain I guess!

     

    Select

    TYPE,

    TYPE2,

    ATTRIBUTE1,

    LOCATION =

     CASE

    WHEN (TYPE = 'Switch' AND TYPE2 = 'out') THEN 'inwards reversal'

    WHEN (TYPE = 'Switch' AND TYPE2 = 'in') THEN 'outwards reversal'

    ELSE TYPE

    END,

    LINESA =

    CASE

    WHEN (LOCATION = 'inwards reversal' AND ATTRIBUTE1 = 'X') THEN 'X LINE'

    WHEN (LOCATION = 'outwards reversal' AND ATTRIBUTE1 = 'Y') THEN 'Y LINE'

    ELSE 'NO LINES'

    END

    FROM Datatable

    .......

    query analyzer doesn't like this so what should I do, im trying to avoid having 50 lines of nested case statements

    Thanks

    Tristan

  • What is the error message?

  • And what's the DDL, sample data and expected results?

  • the error message im getting is

    'Invalid column name'

    refering to BOTH THE COLUMNS  where ive used CASE

     

     

     

     

  • oh sorry. You can't do this :

    location = 'blad',

    case when location...

    The column doesn't exists at this point of the select.

  • Cheers for thatI guess I better get cracking on all those case statements then ... lol should be fun (ps theres about 12 other case statements in the actual report so its going to be a fun afternoon)

     

    thanks again have a great weekend

  • Well maybe you can do it with 1 or 2 derived table :

    Select case dtL1.One when '1' then 1 else 0 end as One, Case dtL1.Two when '-2' then -2 else 2 end as Two from

    (Select case 1 when 1 then '1' else '0' end as One, case 2 when 2 then '2' else '-2' end as Two) dtL1

    That way you can put all 1st level calculation in a single select, then resut them in a the level1 derived table.... That might shorten the code a little bit.

  • You have to change the expressions from the lables to the expressions that evalutes to those labels..

    Select

    TYPE,

    TYPE2,

    ATTRIBUTE1,

    LOCATION =

     CASE

    WHEN (TYPE = 'Switch' AND TYPE2 = 'out') THEN 'inwards reversal'

    WHEN (TYPE = 'Switch' AND TYPE2 = 'in') THEN 'outwards reversal'

    ELSE TYPE

    END,

    LINESA =

    CASE

    WHEN (TYPE = 'Switch' AND TYPE2 = 'out' AND ATTRIBUTE1 = 'X') THEN 'X LINE'

    WHEN (TYPE = 'Switch' AND TYPE2 = 'in' AND ATTRIBUTE1 = 'Y') THEN 'Y LINE'

    ELSE 'NO LINES'

    END

    FROM Datatable

     

    /Kenneth

  • That's what he wants to avoid... I can imagine that case statement becoming verrrrrryyy long .

  • In that case an option may be to nest derived tables. That way you only have to define the expression once, then you can refer to it's label in the outer layers.

    SELECT  x.TYPE,

            x.TYPE2,

            x.ATTRIBUTE1,

            x.LOCATION

            LINESA =

            CASE

            WHEN (x.LOCATION = 'inwards reversal' AND ATTRIBUTE1 = 'X') THEN 'X LINE'

            WHEN (x.LOCATION = 'outwards reversal' AND ATTRIBUTE1 = 'Y') THEN 'Y LINE'

            ELSE 'NO LINES'

            END

    FROM (

            Select TYPE,

                   TYPE2,

                   ATTRIBUTE1,

                   LOCATION =

                   CASE

                   WHEN (TYPE = 'Switch' AND TYPE2 = 'out') THEN 'inwards reversal'

                   WHEN (TYPE = 'Switch' AND TYPE2 = 'in') THEN 'outwards reversal'

                   ELSE TYPE

                   END

             FROM  Datatable

          ) x

    FROM  Datatable

    If it's the way to go depends on the actual query, though.. But I've done this kind of nesting up to 5-6 layers very succesfully.´

    Naturally, your mileage may vary...

    /Kenneth

     

  • Select case dtL1.One when '1' then 1 else 0 end as One, Case dtL1.Two when '-2' then -2 else 2 end as Two from

    (Select case 1 when 1 then '1' else '0' end as One, case 2 when 2 then '2' else '-2' end as Two) dtL1

    .

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply