November 19, 2003 at 11:21 pm
Greetings.
I have this select which dies when I add the ELSE part of the CASE in.
The 3 fields being selected are INT, VARCHAR, VARCHAR
When leaving the else part out, the order by works and proper results are shown.
When including the ELSE I get an error.
quote:
Server: Msg 245, Level 16, State 1, Line 7Syntax error converting the varchar value 'TESTING' to a column of data type int.
The value "testing" is the first primaryFirstName in the table. CLientID 1
Why would it allow me to have two varchar fields as the order by and not a mixed set?
Order by any single field works fine.
Declare
@vOrderInt
Set @vOrder = 1
Select ClientID, PrimaryFirstName, PrimarySurname From Client
Order By
Case
When @vOrder = 1 Then PrimaryFirstName
When @vOrder = 2 Then PrimarySurName
--Else ClientID
End
asc
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
November 20, 2003 at 12:02 am
all the fields will have to be the same type for this to work..
a workaround would be the following query
Set @vOrder = 1
Select ClientID, PrimaryFirstName, PrimarySurname
From Client
Order By
CASE When @vOrder = 1 Then PrimaryFirstName END,
CASE When @vOrder = 2 Then PrimarySurName END,
CASE When @VORder = 3 Then ClientID END
asc
November 20, 2003 at 12:19 am
Thanks!
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply