Nested case - 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

General SQL Server Forums

New to SQL Server Programming

Nested case

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-05-04 : 15:39:48

I'm not sure if this is possible but i'm trying to nest a case statement but keep on getting any error:
Incorrect syntax near the keyword 'Then'

Query:
SELECT F_DIVISION_NO,
Bound,
Primary_SIR,
Case When Bound Then Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end)end as Test

From
(
Select
DBO.THIT_RATIO_DETL.F_DIVISION_NO,
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,

Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) as Primary_SIR

FROM dbo.THIT_RATIO_DETL
Group by F_DIVISION_NO
) t

Is it possible to do a nested case statement?

Thanks!

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-04 : 16:01:04

You can do nested CASE statements in SQL. The problem with your query is that you are missing a condition in your first CASE statemenet:

SELECT F_DIVISION_NO,
Bound,
Primary_SIR,
Case When Bound = 0 Then Sum

SQL Server Helper
http://www.sql-server-helper.com

jpockets
Starting Member

45 Posts

Posted - 2007-05-04 : 16:19:05

Thanks for the advice.....

I changed the "nested" column to have a condition but i'm getting an error:
Incorrect syntax near ')'

New Query:

SELECT F_DIVISION_NO,
Bound,
Primary_SIR,
CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) ELSE 0 END)as test

From
(
Select
DBO.THIT_RATIO_DETL.F_DIVISION_NO,
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,

Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) as Primary_SIR

FROM dbo.THIT_RATIO_DETL
Group by F_DIVISION_NO
) t

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 00:14:15

SELECT F_DIVISION_NO,
Bound,
Primary_SIR,
(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = 'B' or dbo.THIT_RATIO_DETL.STATUS_CD = 'K' Then
Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN
dbo.THIT_RATIO_DETL.DED_OCR_AM else
dbo.THIT_RATIO_DETL.DED_AGR_AM end)
ELSE 0 END)as test

jpockets
Starting Member

45 Posts

Posted - 2007-05-07 : 12:05:55

I had to move the Nested case statement to the inner sql and i'm getting an error that says:
Column 'dbo.THIT_RATIO_DETL.STATUS_CD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If i remove this column:

(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = 'B' or dbo.THIT_RATIO_DETL.STATUS_CD = 'K' Then 
Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN 
dbo.THIT_RATIO_DETL.DED_OCR_AM else 
dbo.THIT_RATIO_DETL.DED_AGR_AM end) 
ELSE 0 END)as test

it works fine, in my other column i have the same reference to the status cd, so I’m not sure why I have to put the status code in the group by just for this column and not the others… if i add the status cd to the group by statement it throws my results off, if anybody has any suggestions it's greatly appreciated...

Full Query:

SELECT F_DIVISION_NO,
       Bound,
       Primary_SIR,
       Test
       
       
      
      

From 
            (
            Select 
            DBO.THIT_RATIO_DETL.F_DIVISION_NO,
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,
            
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) as Primary_SIR,
            (CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = 'B' or dbo.THIT_RATIO_DETL.STATUS_CD = 'K' Then 
Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN 
dbo.THIT_RATIO_DETL.DED_OCR_AM else 
dbo.THIT_RATIO_DETL.DED_AGR_AM end) 
ELSE 0 END)as test
            
            
            
            
            
            
FROM  dbo.THIT_RATIO_DETL 
Group by F_

DIVISION_NO
) t

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 12:20:02

[code]Sum(
CASE
WHEN (dbo.THIT_RATIO_DETL.STATUS_CD = 'B' or dbo.THIT_RATIO_DETL.STATUS_CD = 'K') and dbo.THIT_RATIO_DETL.DED_AGR_AM = 0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM
WHEN (dbo.THIT_RATIO_DETL.STATUS_CD = 'B' or dbo.THIT_RATIO_DETL.STATUS_CD = 'K') and dbo.THIT_RATIO_DETL.DED_AGR_AM <> 0 THEN dbo.THIT_RATIO_DETL.DED_AGR_AM
ELSE 0
END
)as test[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jpockets
Starting Member

45 Posts

Posted - 2007-05-07 : 12:46:05

Thank-you!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources