Select case Logic

Question

Post reply

Select case Logic

KGJ-Dev

SSCertifiable

Points: 5777
More actions
February 2, 2017 at 8:24 am

#334656

Hi,

Below are the sample to play with. Assume always there will be 2 rows in the temp variable.

DECLARE @test-2 TABLE ( Id INT ,IsRegistered TINYINT ,IsAccepted TINYINT ,DBContent VARCHAR(max) ); DECLARE @T1Id INT ,@T2Id INT ,@T1Passed TINYINT ,@T1DBContent VARCHAR(max) ,@T2Passed TINYINT ,@T2DBContent VARCHAR(max)
INSERT INTO @test-2 SELECT 1 ,1 ,0 ,'Test1' UNION ALL SELECT 2 ,1 ,1 ,'Test2'; SELECT @T1Id = CASE WHEN Id = 1 THEN 1 ELSE 0 END ,@T1Passed = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE 0 END ,@T1DBContent = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE '' END ,@T2Id = CASE WHEN Id = 2 THEN 2 ELSE 0 END ,@T2Passed = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE 0 END ,@T2DBContent = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE '' END FROM @test-2 SELECT @T1Id ,@T1Passed ,@T1DBContent ,@T2Id ,@T2Passed ,@T2DBContent --Expeced Result
select 1 as T1Id ,1 as T1Passed ,'Test1' as T1DBContent, 2 as T2Id ,1 as T2Passed,'Test2' as T2DBContent

Not sure what am i doing wrong. not getting the expected result. Any suggestion or correction please

Viewing 6 posts - 1 through 5 (of 5 total)

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

Thom A SSC Guru Points: 99009 More actions · Answer 1

The result set you're getting is to be expected. You're trying to assign multiple rows to a single variable. Thus the result from the final row is assign (In this case 2, 1, 1, 'Test2').

What is your goal here. Do you have to assign these values to Variables and select from them, or can you do it simply with a SELECT statement from your table?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

spider007 SSC Veteran Points: 226 More actions · Answer 2

spider007

SSC Veteran

Points: 226

February 2, 2017 at 8:41 am

#1926364

I think, problem is that @test-2 table contains 2 rows. So the variable assign is applied twice.
In this case @T1Id, while reading first row of @test-2, the value is set to 1, but after read second row the condition Id = 1 is false and @T1Id is set to 0.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nemecek.jano - Thursday, February 2, 2017 8:41 AM

I think, problem is that @test-2 table contains 2 rows. So the variable assign is applied twice.
In this case @T1Id, while reading first row of @test-2, the value is set to 1, but after read second row the condition Id = 1 is false and @T1Id is set to 0.



-- Your variables will be reset for every row encountered.

-- So initialise them to the "No" value,

-- and only change them when the correct set of valus are encountered on a row.

-- BEWARE! For row 2, IsAccepted = 1, but you're checking for IsAccepted = 0 in the code.



DECLARE @test-2 TABLE (



    Id INT



    ,IsRegistered TINYINT



    ,IsAccepted TINYINT



    ,DBContent VARCHAR(max)



    );



DECLARE @T1Id INT



    ,@T2Id INT



    ,@T1Passed TINYINT



    ,@T1DBContent VARCHAR(max)



    ,@T2Passed TINYINT



    ,@T2DBContent VARCHAR(max)



INSERT INTO @test-2 (Id, IsRegistered, IsAccepted, DBContent)



SELECT 1, 1, 0, 'Test1'



UNION ALL



SELECT 2, 1, 1, 'Test2';



SELECT 



	@T1Id = CASE WHEN Id = 1 THEN 1 ELSE 0 END,



    @T1Passed = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE 0 END,



    @T1DBContent = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE '' END,



    @T2Id = CASE WHEN Id = 2 THEN 2 ELSE 0 END,



    @T2Passed = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE 0 END,



    @T2DBContent = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE '' END



FROM @test-2



SELECT 



	@T1Id = 0,



    @T2Id = 0,



    @T1Passed = 0,



    @T1DBContent = '',



    @T2Passed = 0,



    @T2DBContent = ''



SELECT 



	@T1Id = CASE WHEN Id = 1 THEN 1 ELSE @T1Id END,



    @T1Passed = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE @T1Passed END,



    @T1DBContent = CASE WHEN Id = 1 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE @T1DBContent END,



    @T2Id = CASE WHEN Id = 2 THEN 2 ELSE @T2Id END,



    @T2Passed = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE @T2Passed END,



    @T2DBContent = CASE WHEN Id = 2 AND IsRegistered = 1 AND IsAccepted = 0 THEN DBContent ELSE @T2DBContent END



FROM @test-2



SELECT @T1Id



    ,@T1Passed



    ,@T1DBContent



    ,@T2Id



    ,@T2Passed



    ,@T2DBContent



--Expected Result (not really, see notes ^^)



select 1 as T1Id ,1 as T1Passed  ,'Test1' as T1DBContent, 2 as T2Id ,1 as T2Passed,'Test2' as T2DBContent

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden