November 29, 2007 at 4:07 am
I'm trying to execute something like this:
DECLARE @Ind int
, @Result nvarchar(50)
SELECT @Result =
CASE @Ind
WHEN 1 THEN BRSNummer
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
WHEN 2 THEN PLSPersoonsNummer
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
ELSE TimeID
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
END
I receive a syntaxerror everytime. I have tried several variations with CASE, but can't get it to work. So now I use IF..ELSE, but it vexes me.
Any hint?
Greetz,
Hans Brouwer
November 29, 2007 at 4:30 am
Hi,
Hans the case is on which column of the table Centraal.PersoonSleutelsIntern
Regards,
Ahmed
November 29, 2007 at 4:44 am
What about ...
DECLARE
@Ind int
,@Result nvarchar(50)
SELECT
CASE @Ind
WHEN 1 THEN @Result = BRSNummer
WHEN 2 THEN @Result = PLSPersoonsNummer
ELSE @Result = TimeID
END
FROM
Centraal.PersoonSleutelsIntern
WHERE
COPPersoonID = 1
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 4:46 am
the case statment is for a field, I think you thought you needed to identify the table and where for each case, and that's not true:
this is syntactically correct:
DECLARE @Ind int
, @Result nvarchar(50)
--@Ind is declared above, but since it is unassigned, this test sql would leave it NULL
--I assumme this is a snippet and the value would be passed in?
--SET @Ind=1
SELECT @Result =
CASE @Ind
WHEN 1 THEN BRSNummer
WHEN 2 THEN PLSPersoonsNummer
ELSE TimeID END
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
Lowell
November 29, 2007 at 4:49 am
Hi Jason,
When you set a value to a variable you need to use SET 😛
November 29, 2007 at 4:57 am
Ahmed,
Actually that's not true. However, Lowell is correct in that the @Result= needs to appear directly after the SELECT.
So...
SELECT
CASE @Ind
WHEN 1 THEN @Result = BRSNummer
WHEN 2 THEN @Result = PLSPersoonsNummer
ELSE @Result = TimeID
END
FROM
Centraal.PersoonSleutelsIntern
WHERE
COPPersoonID = 1
is incorrect, and ...
SELECT
@Result =
CASE @Ind
WHEN 1 THEN BRSNummer
WHEN 2 THEN PLSPersoonsNummer
ELSE TimeID
END
FROM
Centraal.PersoonSleutelsIntern
WHERE
COPPersoonID = 1
is correct
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 5:01 am
Sorry Jason the 2 way SET and SELECT 😛
November 29, 2007 at 5:07 am
Ahmed Bouzamondo (11/29/2007)
Sorry Jason the 2 way SET and SELECT 😛
Ahmed, I'm not clear on what you mean here.:ermm:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 5:13 am
Hi Jason,
Me too I think Lowell code is the rifht way.
Just check http://msdn2.microsoft.com/en-us/library/ms187953.aspx
Have a good day
I have to go to the office it's 07:15 local time
Regards,
Ahmed
November 29, 2007 at 10:20 am
Tnx for the help, all. I'll have to look into this tomorrow, it is still not clear to me, but I'll get to it.
Tnx again,
Greetz,
Hans Brouwer
November 30, 2007 at 3:33 am
I get this strange message when using the CASE construction as given by Ahmed. 1 of the possible fields to select is datatype UniqueIdentifier. When I run this statement:
DECLARE @Ind int ,
@Result nvarchar(50)
SET @Ind = 3
SELECT @Result =
CASE @Ind
WHEN 1 THEN BRSNummer
WHEN 2 THEN PLSPersoonsNummer
ELSE CRMID
END
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
SELECT @Result
I get this message:
Msg 206, Level 16, State 2, Line 4
Operand type clash: uniqueidentifier is incompatible with int
I do not understand this. To begin with, the @Result variable is NOT integer. When I use an IF statement @Result accepts the uniqueidentifier without a problem.
In the CASE statement I cannot see a connection between uniqueidentifier and either @Ind or @PersoonID.
Can Any1 shed a light on this? When not including the uniqueidentifier the statement works correctly, much more elegant then a series of IF-statements.
Greetz,
Hans Brouwer
November 30, 2007 at 4:02 am
Is the column COPPersoonID a unique identifier?
A unique identifier is a 16 byte long value in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (can be generated by NEWID() function). The condition WHERE COPPersoonID = 1 forces the optimizer to convert the GUID to integer type (as integer type has higher precedence over GUID type), which can't be done when having GUIDs.
--Ramesh
November 30, 2007 at 6:08 am
COPPersoonID is INTEGER, it's the key of that specific table. CRMID is the unique-identifier datatype, just an attribute in that table.
Greetz,
Hans Brouwer
November 30, 2007 at 6:31 am
Well, the CASE statement could only return value with only a single data type. And also comparing an integer with a GUID is not allowed (as integer has higher precedence over GUID and GUID is not a valid integer), which is the case here. You have to convert the individual outputs to the compatible types (i.e varchar).
DECLARE @Ind INT,
DECLARE @Result NVARCHAR(50)
SET @Ind = 3
SELECT @Result = CASE @Ind
WHEN 1 THEN CONVERT( NVARCHAR(50), BRSNummer )
WHEN 2 THEN CONVERT( NVARCHAR(50), PLSPersoonsNummer )
ELSE CONVERT( NVARCHAR(50), CRMID )
END
FROM Centraal.PersoonSleutelsIntern
WHERE COPPersoonID = 1
SELECT @Result
--Ramesh
November 30, 2007 at 7:00 am
OK, got it, tnx for explaining... but I don't get it. As far as I can tell nowhere is there an EXPLICIT comparison between the Integer(either @Ind or @PersoonID) and the guid datatype. Nowhere in the explaination of the CASE statement in BOL, or a Programmers Reference Guide I have, is there mentioning of the 2 statements in the CASE function between the THEN operator.
As mentioned earlier, using an IF statement does not cause this problem; now, I do realize there is a stark difference between the 2 constructions. But I really would like to know where the 2 datatypes are compared to each other...
But tnx again for the solution, Ahmed.
Greetz,
Hans Brouwer
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply