Using Case in a where clause 1

[Trybbe]

Hi May you please help with the query below I am at odds as to what the problem is, it fails on a case statement at the end of the query

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

 

[bborissov]

..............
 and (dt.Headcount = [COLOR=red]'TransferCluster'[/color] OR
      (@Consolidate  != [COLOR=red]'(Retail Consolidate)'[/color] AND dt.Headcount = [COLOR=red]'TransferConsolidate'[/color]))
.......

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Trybbe]

Hi Borrisov

Thank you for the answer it worked well, I achieved the desired results