March 14, 2003 at 5:21 pm
Hello,
I have four CASE statements as part of a SQL Server 2000 query (these will be worked into a View). The fourth CASE statement needs to add the values arrived at by the other three CASE statements in its ‘ELSE’ condition. The CASE statements are as follows (focus on the first three CASE statement names and the ELSE conditon of the fourth CASE statement):
"LSScore" = Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End,
"RScore" = Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End,
"WScore" = Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End,
"OScore" = Case
When TS.RRaw = 0 Then LSScale
Else (LSScore + RScore + WScore)
End
Notice that the last CASE statement called “OScore” attempts to take the result of ‘LSScore’, ‘RScore’, ‘WScore’ and add them together in the ELSE condition. The problem is, these CASE statement names are not real fields. When I attempt to run the query, I get a ‘Server: Msg 207, Level 16’ telling me that each of these names is not a valid column name. I’m hoping to avoid having to set up calculated fields in a table.
Is there any way I can have a fourth CASE statement that will allow me to add the results of the other CASE statements, or do I need to take a different approach?
Thanks for your help!
CSDunn
March 15, 2003 at 1:59 am
Two choices as I see it. First is to wrap all of your query, except the OScore column, in a subquery and then do a SELECT from that. Something like this:
SELECT a.LSScore, a.RScore, a.WScore, "OScore" = Case
When a.RRaw = 0 Then a.LSScale
Else (a.LSScore + a.RScore + a.WScore)
End
FROM (SELECT "LSScore" = Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End,
"RScore" = Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End,
"WScore" = Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End,
TS.RRaw
...
FROM ...) AS a
Something like that, I think you get the picture. The other choice I see is to exchange the 'calculated' columns in the last case expression with the actual case expressions above.
"LSScore" = Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End,
"RScore" = Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End,
"WScore" = Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End,
"OScore" = Case
When TS.RRaw = 0 Then LSScale
Else (Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End + Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End + Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End)
End
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 17, 2003 at 9:57 am
Thanks, I will try this. CSDunn
quote:
Two choices as I see it. First is to wrap all of your query, except the OScore column, in a subquery and then do a SELECT from that. Something like this:SELECT a.LSScore, a.RScore, a.WScore, "OScore" = Case
When a.RRaw = 0 Then a.LSScale
Else (a.LSScore + a.RScore + a.WScore)
End
FROM (SELECT "LSScore" = Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End,
"RScore" = Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End,
"WScore" = Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End,
TS.RRaw
...
FROM ...) AS a
Something like that, I think you get the picture. The other choice I see is to exchange the 'calculated' columns in the last case expression with the actual case expressions above.
"LSScore" = Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End,
"RScore" = Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End,
"WScore" = Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End,
"OScore" = Case
When TS.RRaw = 0 Then LSScale
Else (Case
When TS.Spanish = 1 Then (TS.LSRaw)*1
When TS.RRaw = 0 Then TS.LSScale
Else (TS.LSScale) *.5
End + Case
When TS.Spanish = 1 Then (TS.RRaw)*1
Else (TS.RScale)*.25
End + Case
When TS.Spanish = 1 Then (TS.WRaw)*1
Else (TS.WScale)*.25
End)
End
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply