Case statment in where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Case statment in where clause

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-08 : 17:41:34

I want to accomplish a query similiar to the following with the case statement creating the where clause, but it does not seem to work.

This is what I am trying to accomplish in a simple format

create table a( col1 int, col2 int,col3 int)

Select * from a
WHERE case a.Col1
When 1 then col2 = formula1
when 2 then col3 = formula2
end

Both "WHEN" lines work fine if I use them individually, it's just in the case statment that t-sql will not allow.

My actual query is below, but for simplicity please use the query above for explaining.

select c.EmployerID,a.GenerationDateID,a.GenerationDate,b.PlanID,e.EmployeeID,
dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) as PeriodToBill,
c.Rate, c.EmployerPlanRateID,
dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) as DateActive,
f.ContractID,
f.PlanBenWWaitID
From TAC_GenerationDate a inner join TMI_Plan b
on a.BenefitTypeID = b.BenefitTypeID inner join
tmi_EmployerPlanrate c on b.PlanID = c.PlanID inner join
TMI_BenefitType d on a.BenefitTypeID = d.BenefitTypeID inner join
TMI_EmployeePlan e on c.PlanID = e.PlanID and e.EmployerID = c.EmployerID inner join
TMI_EmployerPlanBenWWait f on c.ContractID = f.ContractID and b.BenefitTypeID = f.BenefitTypeID
where d.SetRateAtEmployee = 2 and c.EmployerPlanRateID =
(Select Top 1 a1.EmployerPlanRateID From TMI_EmployerPlanRate a1 where a1.EffectiveDate <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) and a1.EmployerID = c.EmployerID and a1.PlanID = c.PlanID order by a1.EffectiveDate desc)
and
(Case f.BillCurrent
when 1 then dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc )) <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)
When 2 then dateadd(m, datediff(m, 0,dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc ))), 0) <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID)
end)
order by e.EmployeeID,b.PlanID,a.generationDate

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-08 : 18:25:35

Select * from a
WHERE
(a.Col1 = 1 and col2 = formula1)
or
(a.Col1 2 then col3 = formula2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-08 : 19:07:48

******8
Select * from a
WHERE
(a.Col1 = 1 and col2 = formula1)
or
(a.Col1 2 then col3 = formula2)
*********

Is the syntex correct on this? The first line is using "and", and the second line is using "then" is it supposed to read

******8
Select * from a
WHERE
(a.Col1 = 1 then col2 = formula1)
or
(a.Col1 2 then col3 = formula2)
*********

will this type of statment work in t-sql w/o a "if" before it to produce case like results?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-08 : 21:22:20

I think Nigel had a typo ... should be:

Select * from a
WHERE
(a.Col1 = 1 and col2 = formula1)
or
(a.Col1=2 ~~then~~ and col3 = formula2)

- Jeff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-08 : 22:01:30

I do not believe this will work in my scenerio (you can check out the actual syntex in my first post), but I will give it a shot first thing in the morning. I'll post the results.

AugustinPrasanna
Starting Member

3 Posts

Posted - 2006-08-09 : 03:57:00

Is this what you are looking for?

select
c.EmployerID,a.GenerationDateID,a.GenerationDate,b.PlanID,e.EmployeeID,
dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) as PeriodToBill,
c.Rate, c.EmployerPlanRateID,
dbo.EmployeeActiveinPeriod(e.EmployeeID,dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID),d.BillingCycleID) as DateActive,
f.ContractID,f.PlanBenWWaitID
From
TAC_GenerationDate a inner join TMI_Plan b
on a.BenefitTypeID = b.BenefitTypeID
inner join
tmi_EmployerPlanrate c
on b.PlanID = c.PlanID
inner join
TMI_BenefitType d on a.BenefitTypeID = d.BenefitTypeID
inner join
TMI_EmployeePlan e on c.PlanID = e.PlanID and e.EmployerID = c.EmployerID
inner join
TMI_EmployerPlanBenWWait f on c.ContractID = f.ContractID and b.BenefitTypeID = f.BenefitTypeID
where
d.SetRateAtEmployee = 2 and c.EmployerPlanRateID =
(Select Top 1 a1.EmployerPlanRateID From TMI_EmployerPlanRate a1 where a1.EffectiveDate <= dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) and a1.EmployerID = c.EmployerID and a1.PlanID = c.PlanID order by a1.EffectiveDate desc)
and
(
dbo.AdjustDate(d.BillingCycleID,d.PeriodtoBill,a.GenerationDate,d.BenefitTypeID) >
Case when f.BillCurrent = 1 then dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc ))
When f.BillCurrent = 2 then dateadd(m, datediff(m, 0,dateAdd(Month,f.WaitingPeriod,(select top 1 zz.EffectiveDate from TMI_EmployeeStatus zz where zz.EmployeeID = e.EmployeeID and zz.EstatusID =1 and zz.StatusID = 1 order by zz.effectiveDate asc ))), 0)
end)
order by e.EmployeeID,b.PlanID,a.generationDate

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 04:15:35

I see a problem with:

Select * from a
WHERE case a.Col1
When 1 then col2 = formula1
when 2 then col3 = formula2
end

...because you can't just say:

select * from a where true

Why not do something like:

Select * from a
WHERE 1= case a.Col1
When 1 then case when col2 = formula1 then 1 else 0 end
when 2 then case when col3 = formula2 then 1 else 0 end
else 0
end

..so effectively you're returning 1 from the case statement when the statement holds, or 0 when it doesn't.

Rob Farley
http://robfarley.blogspot.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-09 : 07:13:55

quote:
Originally posted by Vinnie881

I do not believe this will work in my scenerio (you can check out the actual syntex in my first post), but I will give it a shot first thing in the morning. I'll post the results.

It will. it's just simple logic.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-09 : 07:16:03

quote:
Originally posted by rob_farley

I see a problem with:

Select * from a
WHERE case a.Col1
When 1 then col2 = formula1
when 2 then col3 = formula2
end

...because you can't just say:

select * from a where true

Why not do something like:

Select * from a
WHERE 1= case a.Col1
When 1 then case when col2 = formula1 then 1 else 0 end
when 2 then case when col3 = formula2 then 1 else 0 end
else 0
end

..so effectively you're returning 1 from the case statement when the statement holds, or 0 when it doesn't.

Rob Farley
http://robfarley.blogspot.com

Simple boolean logic in your where clause is all you need; rarely should you use a case expression there unless absolutely necessary .

- Jeff

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 20:34:32

Jeff - can you give me a real example (perhaps using the AdventureWorks database?) that will work with this?

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 20:41:59

Actually, sorry... let me explain why the case statement... If formulaX does something like a type conversion, which might fail if the thing won't convert, then a case statement will hide it nicely from the optimiser. If you just use boolean logic, SQL may well try to convert something invalid first, and you'll become a cropper.

On the whole, I agree - it allows for indexes, etc. But there are also many times when case is better.

Rob Farley
http://robfarley.blogspot.com

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-09 : 23:24:18

The method in nr's post seemed to work he best. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources