Using Case in a where clause

Question

Post reply

Using Case in a where clause

Trybbe

Hall of Fame

Points: 3967
More actions
December 15, 2009 at 12:59 am

#135998

Hi May you please help with the query below I am at odds as to where the error is
Declare
@StartPeriod Numeric(18,0),
@EndPeriod Numeric(18,0),
@Consolidate Varchar(200),
@TransferType Varchar(200),
@EEOccupLevels Varchar(200)
Set @StartPeriod = 200901
Set @EndPeriod = 200911
Set @Consolidate = '(Retail Consolidate)'
Set @TransferType = 'TransferIn'
Begin
select re.Cluster, re.Consolidate, re.Level3, re.Division,
re.Region, re.Area, re.Branch, re.CostCenter, dt.Staffno,
case when earningrange = 'ER 1' then ' ER 1'
when earningrange = 'ER 2' then ' ER 2'
when earningrange = 'ER 3' then ' ER 3'
when earningrange = 'ER 4' then ' ER 4'
when earningrange = 'Maximum' then ' Max'
when earningrange = 'Minimum' then ' Min'
when earningrange = 'None' then 'Undefined'
when earningrange like '% Matched' then 'Undefined' else EarningRange end as Earning_Range
from DTIHeadcount dt
inner join ReportingStructure re ON dt.CostCenter = re.CostCenter
left outer join Industries ON dt.HireIndustryID = Industries.IndustryID
where (dt.Period between @StartPeriod and @EndPeriod)
and (re.Consolidate IN (@Consolidate) OR (@Consolidate = '(Retail Consolidate)') )
and (dt.TransferType IN (@TransferType))
and (dt.Headcount In (case when @Consolidate = '(Retail Consolidate)' Then ('TransferCluster')
when @Consolidate != '(Retail Consolidate)' Then 'TransferCluster'',' 'TransferConsolidate' end))
order by re.Consolidate
End

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

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

Mike_D Hall of Fame Points: 3295 More actions · Answer 1

An explanation of what you are trying to achieve would help as would the DDL. It looks like the error is in this:

'TransferCluster'',' 'TransferConsolidate'

These are two separate strings, given the use of quotation marks, which I don't think is what you are trying to achieve. So you could fix this by re-coding as

'TransferCluster'',' + 'TransferConsolidate'

but that would generate the rather odd result of TransferCluster',TransferConsolidate which I suspect is not what you are after either. Looking at the rest of the code I'd take a stab at this being what you really want:

re-code 'TransferCluster'',' 'TransferConsolidate' as 'TransferCluster,TransferConsolidate'

Mike

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You can use CASE in a WHERE clause, but that's not the problem - you are trying to construct a list on-the-fly for the IN operator. The syntax of your CASE statement

and (dt.Headcount In (

case

when @Consolidate = '(Retail Consolidate)' Then ('TransferCluster')

when @Consolidate != '(Retail Consolidate)' Then 'TransferCluster'',' 'TransferConsolidate' end)

)

is giving errors, but it won't work anyway - constructing a list as a string on the fly for an IN operator requires dynamic SQL.

As Mike points out, if you explain more clearly what you are trying to do (for the whole WHERE clause), it will be much easier to help.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Trybbe Hall of Fame Points: 3967 More actions · Answer 3

Thanks for the feedback guys

What i was trying to achieve is:

Evaluate the contents of variable @Consolidate, if = (Retail Consolidate) then headcount = transfercluster else headcount In ('TransferCluster', 'TransferConsolidate').

A solution from another forum was:

and ((dt.Headcount = 'TransferCluster') OR

(@Consolidate != '(Retail Consolidate)' AND dt.Headcount IN ('TransferCluster','TransferConsolidate')))

and managed to get the desired results.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What are you trying to achieve with the 'dt.TransferType IN (@TransferType)' syntax? Using a variable in an in is the same as directly comparing the column to the variable with an =. It does not split a comma-delimited string and cehck against each one.

use master

go

DECLARE @tables VARCHAR(50)

SET @tables = '''spt_monitor'',''spt_values''' -- matching 2 tables in master

SELECT * FROM sys.tables WHERE name IN (@tables) -- 0 rows returned.

You need either dynamic SQL or a string splitting function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Something like this:

WHERE headcount = 'transfercluster'

OR headcount = CASE WHEN @Consolidate <> '(Retail Consolidate)' THEN 'TransferConsolidate' ELSE CAST(NEWID() AS CHAR(36)) END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Trybbe Hall of Fame Points: 3967 More actions · Answer 6

Well this code is for a report in ssrs.

the variable @TranferType will only have 1 value at a time, so the = is still ok in that instance. The problem I had was with headcount. where it could either be Headcount = 'transferCluster' or headcount IN ('TransferCluster', 'TransferConsolidate') that matter has since been resolved with :

and ((dt.Headcount = 'TransferCluster') OR (@Consolidate != '(Retail Consolidate)'

AND dt.Headcount IN ('TransferCluster','TransferConsolidate')))

Thanks guys