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?

    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

  • thava

    SSCrazy

    Points: 2275

    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]

  • Dwain Camps

    SSC Guru

    Points: 86883

    thava (1/8/2014)


    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

    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

  • 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
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

    An easy task for 3 points! 🙂

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

  • Chris Cradock

    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]
    Connect to me on LinkedIn

  • 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]
    Connect to me on LinkedIn

  • 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

  • dcraday

    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.

  • dcraday

    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)

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