September 9, 2005 at 6:43 am
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
September 9, 2005 at 6:47 am
What is the error message?
September 9, 2005 at 6:49 am
And what's the DDL, sample data and expected results?
September 9, 2005 at 6:59 am
the error message im getting is
'Invalid column name'
refering to BOTH THE COLUMNS where ive used CASE
September 9, 2005 at 7:04 am
oh sorry. You can't do this :
location = 'blad',
case when location...
The column doesn't exists at this point of the select.
September 9, 2005 at 7:23 am
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
September 9, 2005 at 7:30 am
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.
September 9, 2005 at 7:32 am
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
September 9, 2005 at 7:34 am
That's what he wants to avoid... I can imagine that case statement becoming verrrrrryyy long .
September 9, 2005 at 7:47 am
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
September 9, 2005 at 7:51 am
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