Adding Case statement into Where

Question

Adding Case statement into Where

Luv SQL

SSCommitted

Points: 1544
More actions
June 15, 2021 at 5:18 pm

#3896574

I have a crystal report I'm trying to create a sp for the data so I can use in SSRS. This is the statement which works:
SELECT p.JCCo,p.Job,substring(p.Phase,2,2) as Division,rtrim(ltrim(p.Phase)) as Phase,p.Description as PhaseDesc,j.Description as JobName,
j.udConstructionStart as ConstructionStart, j.udForecastedComplete as ForecastedComplete
FROM JCJP p inner join JCJM j
ON p.JCCo=j.JCCo and p.Job=j.Job
where p.JCCo=@Company and p.Job=@Job and substring(Phase,1,3) not in (' 00',' 20')
Now I have to add 3 parameters (@Incl17, @Incl18 and @Incl19). If either of these are a Y then those substring(p.Phase,2,2) values of '17', '18' and '19' have to be included as well.
In Crystal Reports this is how it works but cannot seem to get this to work in SQL.
not (Left ({JCJP.Phase},3) in [" 00", " 20"]) and
(if {?Div17}='N' then Left ({JCJP.Phase},3)<>' 17' else 1=1) and
(if {?Div18}='N' then Left ({JCJP.Phase},3)<>' 18' else 1=1) and
(if {?Div19}='N' then Left ({JCJP.Phase},3)<>' 19' else 1=1)
Any ideas? Have tried CASE in many ways but it's not working.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I would probably take this approach (although not that efficient, it is easy to read in my opinion):

where (p.JCCo=@Company and p.Job=@Job and substring(Phase,1,3) not in (' 00',' 20')) AND
(@Incl17='Y' OR (@Incl17='N' AND LEFT(Phase,3) NOT IN (' 17'))) AND 
(@Incl18='Y' OR (@Incl18='N' AND LEFT(Phase,3) NOT IN (' 18'))) AND 
(@Incl19='Y' OR (@Incl19='N' AND LEFT(Phase,3) NOT IN (' 19')))

May not be the most efficient approach, but I think it should work. Depending on how much data those INCL17,18 and 19 variables are filtering out, you may get better performance by sending all of the data back to SSRS and letting SSRS filter the rows.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Luv SQL SSCommitted Points: 1544 More actions · Answer 2

Fantastic! The number of records that each @Incl filters out is minimal.

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

For the pure SQL part, technically this could theoretically perform better since it avoids using functions on the table columns:

WHERE (p.JCCo=@Company and p.Job=@Job AND Phase NOT LIKE ' 00%' AND Phase NOT LIKE ' 20%') AND
    (@Incl17='Y' OR Phase NOT LIKE ' 17%') AND 
    (@Incl18='Y' OR Phase NOT LIKE ' 18%') AND 
    (@Incl19='Y' OR Phase NOT LIKE ' 19%')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".