Unravel Some Complex IF Logic
-
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?
My advice:
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 -
intresting one i have one more option
how about this one
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
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (1/8/2014)
intresting one i have one more optionhow about this one
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?
My advice:
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 -
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
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
An easy task for 3 points! 🙂
We have learned to explicitly terminate each possible flow of the code.
-
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.
-
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
-
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]
Connect to me on LinkedIn -
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 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]
Connect to me on LinkedIn -
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
-
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.
-
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.
-
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 18 total)
You must be logged in to reply to this topic. Login to reply