Need to replace this CASE statement in Where clause

Question

Post reply

Need to replace this CASE statement in Where clause

VoldemarG

Hall of Fame

Points: 3883
More actions
March 15, 2020 at 12:51 pm

#3734512

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;
Likes to play Chess

March 16, 2020 at 11:54 am

This was removed by the editor as SPAM

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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: 3883 More actions · Answer 2

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 ))

Likes to play Chess

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

--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 4 years, 8 months ago by Phil Parkin.
This reply was modified 4 years, 8 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

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: 18354 More actions · Answer 5

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?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

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?