Select case Logic

Question

Select case Logic

KGJ-Dev

SSCertifiable

Points: 5780
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: 99133 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

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

Y.B. SSChampion Points: 11649 More actions · Answer 4

Is this perhaps what you are looking for?

SELECT Id, IsRegistered, IsAccepted, DBContent, CASE WHEN IsRegistered = 1 AND IsAccepted = 0 THEN 1 ELSE 0 END AS Passed

FROM @test-2

SELECT quote FROM brain WHERE original = 1
0 rows returned

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Thanks everyone who tried to help me and Chris M solution worked for me. appreciated your time on this,