Nested Case Statement - 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)

Nested Case Statement

Author

Topic

vbjohn
Starting Member

32 Posts

Posted - 2001-12-13 : 09:18:39

I am wondering if there is a way that we could do a nesting case statement in an SQL Query?

This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END

What I want:

FicaWages = CASE
WHEN ('Sql Statement') = 'GRM' THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END

I hope this is clear enough.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-13 : 09:27:33

What's the problem? I mean, I don't understand why your proposed nested case has the same thing on both sides, but the syntax looks fine to me.

The table and field names, on the other hand...

Edited by - Arnold Fribble on 12/13/2001 09:28:12

vbjohn
Starting Member

32 Posts

Posted - 2001-12-13 : 09:43:40

When I do this...
ELSE (UPR30100.GRWGPRN - ('Sql Statement')) on both sides the 'Sql Statement' is different.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-13 : 11:59:18

If it's only that part that differs, then why the nested case anyway?


FicaWages = CASE
  WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
    >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' 
  WHEN UPR00400.SBJTSSEC = 0 THEN '0' 
  WHEN ('Sql Statement') = 'GRM' THEN (UPR30100.GRWGPRN - ('Sql Statement1'))
  ELSE (UPR30100.GRWGPRN - ('Sql Statement2')) 
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources