Need to replace this CASE statement in Where clause

  • VoldemarG

    Hall of Fame

    Points: 3670

    Need to replace this CASE statement in Where clause with something else (not CASE...), AND/OR (?). Anyway, the task is to get rid of Case statement in Where clause but I am not seeing how. Does anyone have an idea?

    SELECT….<column list>

    FROM  CA_Leaves CL

    WHERE  CL.C_Status     = 'New'

    AND 1 = CASE WHEN @i_PARAM1_field = @i_PARAM3_field THEN 1

    WHEN @i_PARAM1_field <> @i_PARAM3_field AND CL.class_id = @Other_CL_Id THEN 1

    ELSE 0

    END;

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14685

    what attempts have you made so far?

    that is a fairly simple case of replacing with a combination of "and" and "or"

     

  • VoldemarG

    Hall of Fame

    Points: 3670

    I did this

    but test result from resting the entire SP  (before and after)  that this statement is a part of was different, that's why I doubted.

    AND (@i_PARAM1_field = @i_PARAM3_field OR ( @i_PARAM1_field <> @i_PARAM3_field AND CL.class_id = @Other_CL_Id ))

    Voldemar likes to play CHESS (and IS good at it!)

  • Phil Parkin

    SSC Guru

    Points: 244578

    --OK, let me have another attempt at this post!

    Your logic seems solid.

    I think you need to compare the two sets of results, identify the differences and see whether you can make sense of it from there.

    It would be interesting to hear more; please post back with findings.

    • This reply was modified 3 months, 2 weeks ago by  Phil Parkin.
    • This reply was modified 3 months, 2 weeks ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • frederico_fonseca

    SSChampion

    Points: 14685

    As Phil said it seems correct.

    so in order to test it to ensure that the 2 queries work the same way test the individual selects in isolation of the proc - easy to setup - copy and paste code (old and new) and declare/set the variables - when you manage them to give the same results for the different combinations of parameters then you know the code is correct - if proc is giving different results then the error is somewhere else.

  • Jonathan AC Roberts

    SSCoach

    Points: 17293

    SELECT….<column list>
    FROM CA_Leaves CL
    WHERE CL.C_Status = 'New'
    AND (@i_PARAM1_field = @i_PARAM3_field
    OR (@i_PARAM1_field <> @i_PARAM3_field
    AND CL.class_id = @Other_CL_Id))

    Why do you want to get rid of the CASE? Do you think it will be anymore efficient?

  • This was removed by the editor as SPAM

  • Jonathan AC Roberts

    SSCoach

    Points: 17293

    Garland wrote:

    Thanks, my CASE is what I was worried about.  If all I have to do is change the ORs to ANDs, then I'm happy.  I was able to find plenty of examples of CASE within WHERE, but nothing which looked similar to this.

    What exactly were you worried about with the CASE statement?

Viewing 8 posts - 1 through 8 (of 8 total)

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