# Unravel Some Complex IF Logic

• Dwain Camps

SSC Guru

Points: 86883

Comments posted to this topic are about the item Unravel Some Complex IF Logic

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

• thava

SSCrazy

Points: 2275

intresting one i have one more option

``` SELECT @Result = NULL; -- Option 6: SELECT @Result = CASE WHEN @aa =@XXX AND @XXXCount > @AAXXXCount THEN 2 WHEN @aa =@YYY AND @YYYCount > @AAYYYCount THEN 2 WHEN @aa =@ZZZ AND @ZZZCount > @AAZZZCount THEN 2 WHEN @aa =@AAXXX AND @XXXCount = @AAXXXCount THEN 3 WHEN @aa =@AAYYY AND @YYYCount = @AAYYYCount THEN 3 WHEN @aa =@AAZZZ AND @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END; SELECT [Option 6:]=@Result ``` Every rule in a world of bits and bytes, can be bend or eventually be broken
A Visualizer for viewing SqlCommand object script [/url]

• Dwain Camps

SSC Guru

Points: 86883

thava (1/8/2014)

intresting one i have one more option

``` SELECT @Result = NULL; -- Option 6: SELECT @Result = CASE WHEN @aa =@XXX AND @XXXCount > @AAXXXCount THEN 2 WHEN @aa =@YYY AND @YYYCount > @AAYYYCount THEN 2 WHEN @aa =@ZZZ AND @ZZZCount > @AAZZZCount THEN 2 WHEN @aa =@AAXXX AND @XXXCount = @AAXXXCount THEN 3 WHEN @aa =@AAYYY AND @YYYCount = @AAYYYCount THEN 3 WHEN @aa =@AAZZZ AND @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END; SELECT [Option 6:]=@Result ```

Thanks for giving it a try Thava. There are in fact quite a few additional variants but space (and probably imagination) precluded me from including them all.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

• thava

SSCrazy

Points: 2275

it seems the following values are give only 4 is the correct answers

``` DECLARE @AA VARCHAR(12) = 'AAA' ,@XXX VARCHAR(12) = 'AAA' ,@YYY VARCHAR(12) = 'BBB' ,@ZZZ VARCHAR(12) = 'CCC' ,@AAXXX VARCHAR(12) = 'AAA' ,@AAYYY VARCHAR(12) = 'XXBBB' ,@AAZZZ VARCHAR(12) = 'XXCCC' ,@XXXCount INT = 1 ,@YYYCount INT = 1 ,@ZZZCount INT = 1 ,@AAXXXCount INT = 1 ,@AAYYYCount INT = 1 ,@AAZZZCount INT = 1 ,@Result INT; ```

the Expected reslut is 0 but Option1 and option2 are return 3

even my one is also wrong for this value Every rule in a world of bits and bytes, can be bend or eventually be broken
A Visualizer for viewing SqlCommand object script [/url]

SSCarpal Tunnel

Points: 4891

An easy task for 3 points! 🙂

We have learned to explicitly terminate each possible flow of the code.

SSC Veteran

Points: 275

I have an issue with options 1 and 2 - if @aa = @xxx then none of the other 'SET's should happen, but saying @AA = @XXX AND @XXXCount > @AAXXXCount leaves the possibility that the next IF statement triggers, when this wouldn't have been possible in the original code.

• venoym

SSCarpal Tunnel

Points: 4161

Overall a good question.

The answer needs some work though. The declares used make the assumption that you will only have a single value for the @XXX, @YYY, @ZZZZ, etc. as well as all of the @XXCount (and etc) being set to 1. If you use other values then it's impossible to have ANY other answer be correct than option 4.

``` DECLARE @AA VARCHAR(12) = 'AAA' ,@XXX VARCHAR(12) = 'AAA' ,@YYY VARCHAR(12) = 'AAA' --<--note the change here ,@ZZZ VARCHAR(12) = 'CCC' ,@AAXXX VARCHAR(12) = 'XXAAA' ,@AAYYY VARCHAR(12) = 'XXBBB' ,@AAZZZ VARCHAR(12) = 'XXCCC' ,@XXXCount INT = 1 ,@YYYCount INT = 2 --<--note the change here ,@ZZZCount INT = 1 ,@AAXXXCount INT = 1 ,@AAYYYCount INT = 1 ,@AAZZZCount INT = 1 ,@Result INT; -- Much to your chagrin, you encounter the following logic in a SQL SP. -- You may assume that each local variable is DECLAREd and @AA, -- @XXX, @YYY, @ZZZ, @AAXXX, @AAYYY, @AAZZZ each have a value assigned, -- with only @AA required to be NOT NULL. Each @xxxCount variable -- is also assigned an integer value. -- SET @Result = 0; IF @AA = @XXX BEGIN IF @XXXCount > @AAXXXCount SET @Result = 2 --<--Note the result will be 0 because of this statement!! END ELSE IF @AA = @YYY BEGIN IF @YYYCount > @AAYYYCount SET @Result = 2 END ELSE IF @AA = @ZZZ BEGIN IF @ZZZCount > @AAZZZCount SET @Result = 2 END ELSE IF @AA = @AAXXX BEGIN IF @XXXCount = @AAXXXCount SET @Result = 3 END ELSE IF @AA = @AAYYY BEGIN IF @YYYCount = @AAYYYCount SET @Result = 3 END ELSE IF @AA = @AAZZZ BEGIN IF @ZZZCount = @AAZZZCount SET @Result = 3 END -- Identify the equivalent logic from the options shown: -- Option 1: SET @Result = 0; IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2 ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2 --<--using the above declares, the result will be a 2!! ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2 ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3 ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3 ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3 SELECT [Option 1:]=@Result SELECT @Result = NULL; -- Option 2: IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2 ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2 --<--using the above declares, the result will be a 2!! ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2 ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3 ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3 ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3 ELSE SET @Result = 0; SELECT [Option 2:]=@Result SELECT @Result = NULL; -- Option 3: SELECT @Result = CASE @AA WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END --<--using the above declares, the result will be a 0!! WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END END; SELECT [Option 3:]=@Result SELECT @Result = NULL; -- Option 4: SELECT @Result = CASE @AA WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END --<--using the above declares, the result will be a 0!! WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END ELSE 0 END; SELECT [Option 4:]=@Result SELECT @Result = NULL; -- Option 5: SELECT @Result = CASE @AA WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 END --<--using the above declares, the result will be a NULL!! WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 END WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 END WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 END WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 END WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 END ELSE 0 END; SELECT [Option 5:]=@Result ```

• Thomas Abraham

SSChampion

Points: 10761

Let's try to simplify:

"Option 3 will always return an integer value for @Result" is false, as it is not GUARANTEED to provide a specific value for @Result when @AA is not equal to any of the 6 test values.

"Options 3 and 5 are identical" is false, because even if @AA is not equal to any of the six test values, it sets @Result to 0.

"Answers 2 and 3 are both true" is thus false, because Answer 2 is false.

By default, "Options 1, 2 and 4 are equivalent and identical to the original code" is the remaining answer that has not already been proven to be false.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]

• Luis Cazares

SSC Guru

Points: 183581

Thomas Abraham (1/9/2014)

Let's try to simplify:

By default, "Options 1, 2 and 4 are equivalent and identical to the original code" is the remaining answer that has not already been proven to be false.

There's an option where @AA is equal to @XXX and @XXXCount is less than or equal to @AAXXXCount AND @AA is equal to @YYY and @YYYCount is greater than @AAYYYCount, just to give an example.

Venoym has already posted the code that proves it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Thomas Abraham

SSChampion

Points: 10761

Luis Cazares (1/9/2014)

Thomas Abraham (1/9/2014)

Let's try to simplify:

By default, "Options 1, 2 and 4 are equivalent and identical to the original code" is the remaining answer that has not already been proven to be false.

There's an option where @AA is equal to @XXX and @XXXCount is less than or equal to @AAXXXCount AND @AA is equal to @YYY and @YYYCount is greater than @AAYYYCount, just to give an example.

Venoym has already posted the code that proves it.

Understood. I was just using logic to show that, without doing any actual test cases, as written, the only answer that COULD be correct is #4. In essence, this was what I did to arrive at #4 for my own answer.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]

• TomThomson

SSC Guru

Points: 104772

Thomas Abraham (1/9/2014)

Luis Cazares (1/9/2014)

Thomas Abraham (1/9/2014)

Let's try to simplify:

By default, "Options 1, 2 and 4 are equivalent and identical to the original code" is the remaining answer that has not already been proven to be false.

There's an option where @AA is equal to @XXX and @XXXCount is less than or equal to @AAXXXCount AND @AA is equal to @YYY and @YYYCount is greater than @AAYYYCount, just to give an example.

Venoym has already posted the code that proves it.

Understood. I was just using logic to show that, without doing any actual test cases, as written, the only answer that COULD be correct is #4. In essence, this was what I did to arrive at #4 for my own answer.

I took a very slightly different approach: The phrase "most correct" (with "most" in quotes) in the question made me expect that all the answers would be false so that I should look for how many truths were included in each answer, rather than eliminate obviously false answers, which was confirmed by looking at the code and discovering that it was obvious from inspection of the code that only option 4 is correct so that all three answers are false.

Options 3 and 5 are both incorrect since both can return NULL. The cases in which they return NULL are different in the two options, so they are not identical. Options 1 and 2 are incorrect, because they can return 2 or 3 in cases when they should return 0. Option 4, like the original code, will return 0 in every case where the first match found on @AA leads to a failing test on the corresponding count as well as in the case where no match is found on @AA, and it selects the appropriate choice from 2 or 3 in the other cases, so clearly it is correct. Of the three suggested answers, two assert only falsehoods and the other asserts two truths (option 1 is identical to option 2, and option 4 is correct) as well as some falshoods, so of the three this one is the "most" correct, an that was what we were asked to find.

I really like this sort of question. But then I was a great fan of mathematical logics when I was young, so maybe other people will like it less.

Tom

SSC Enthusiast

Points: 126

It depends on how you apply your logic.

Answer 3 cannot be correct since options 1, 2, and 4 are neither identical nor equivalent, because only Option 1 initializes @Result. Plus if @AA = @XXX and @AA = @AAXXX and @XXXCount = @AAXXXCount the original code returns 0 because only the first set of conditions will be evaluated.

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result1 = 2

END

Options 1 and 2 will return 3 because the code will fall down through the Else If's because of the AND until this line:

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result3 = 3

Since Answer 3 is not correct, neither can answer 4 be correct.

Answer 2 cannot be correct because Options 3 and 5 are not identical.

Since we have eliminated 3 answers, only answer 1 is left and is the correct answer by default. BUT Answer 1 is false, so we have no answer at all.

SSC Enthusiast

Points: 126

OH,

If we -- Identify the equivalent logic from the options shown: as in the question, only option 4 is similar and covers all the bases, but it's uglier than the original code, so let's not fix it unless it's broke.

• Koen Verbeeck

SSC Guru

Points: 258955

Nice question Dwain, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

• This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)