can a CASE stmt compare 2 fields in the WHEN ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

can a CASE stmt compare 2 fields in the WHEN ?

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 01:59:17

I am trying to sum a balance field based on the value of 2 different fields. I keep getting an
error. It works when I test on just the a.L4_DESC but has an issue when also testing on the 2nd field b.corp.
Says it does not like the 'and' . Wondering if this possible??



SELECT  account ,
          SUM(CASE a.L4_DESC WHEN 'Liability' and b.corp = '20' THEN 
                    b.balance * -1  ELSE 0 END)  liability
into Temp_bal 
FROM dbo.MonthEndBalances b
group by account
LEFT OUTER JOIN dbo.ACCOUNT a 
ON b.account = a.ACCOUNT
group by b.account

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 05:53:07

case when a.L4_DESC = 'Liability' and b.corp = '20' THEN
...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-18 : 09:00:13

you cant use simple case where you need to check for more than one condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 09:18:01

Thank you Webfred,

but I am still a bit unclear on how I would expand with the SUM. Would you be able to expand your example with the WHEN to incorporate the SUM using my original post?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-18 : 09:23:22

SUM(CASE WHEN a.L4_DESC = 'Liability' and b.corp = '20' THEN
b.balance * -1 ELSE 0 END) liability

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-09-18 : 15:08:47

Thanks! Its working out great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:28:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources