Case when's nested in a case when

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 2005 Forums

Transact-SQL (2005)

Case when's nested in a case when

Author

Topic

Wozer
Starting Member

29 Posts

Posted - 2009-03-23 : 10:30:37

Hi,

I am having an issue with case when statements. I am trying to nest a group of case whens inside a case when to get my result when I run the code I am getting an error that says Incorrect syntax near the keyword 'AS' I have a feeling that I am getting this error because I did the nested case whens wrong, googling hasn't found anything useful for me. Can anyone see where I went wrong? (I trunkated the code for length, which basically just repeats the case whens 6 times)


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE  Procedure [dbo].[spxyz]
AS
DECLARE @ClosedSameStageTable TABLE
( Contract_Name varchar(20),
Year_1 int,
Year_2 int,
Year_3 int,
Year_4 int,
Year_5 int,
Year_6 int,
Year_7 int,
Year_8 int,
Year_9 int,
Year_10 int,
Year_11 int
)

INSERT INTO @ClosedSameStageTable (Contract_Name, Year_1, Year_2, Year_3, Year_4, Year_5, Year_6, Year_7, Year_8, Year_9, Year_10, Year_11)
SELECT Contract_Name,
CASE WHEN Contract_Name = 'xxxxx' THEN
	COALESCE(CASE WHEN YEAR(ClosedDate)='1999'
			THEN COUNT(ClaimNumber) END,0) As Year_1,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
			THEN COUNT(ClaimNumber) END,0) As Year_2,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
			THEN COUNT(ClaimNumber) END,0) As Year_3,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2002'
			THEN COUNT(ClaimNumber) END,0) As Year_4,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2003'
			THEN COUNT(ClaimNumber) END,0) As Year_5,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2004'
			THEN COUNT(ClaimNumber) END,0) As Year_6,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2005'
			THEN COUNT(ClaimNumber) END,0) As Year_7,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2006'
			THEN COUNT(ClaimNumber) END,0) As Year_8,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2007'
			THEN COUNT(ClaimNumber) END,0) As Year_9,		
	COALESCE(CASE WHEN YEAR(ClosedDate)='2008'
			THEN COUNT(ClaimNumber) END,0) As Year_10,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2009'
			THEN COUNT(ClaimNumber) END,0) As Year_11
END

CASE WHEN Contract_Name = 'xxxxxxx' THEN
	COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
			THEN COUNT(ClaimNumber) END,0) As Year_1,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
			THEN COUNT(ClaimNumber) END,0) As Year_2,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2002'
			THEN COUNT(ClaimNumber) END,0) As Year_3,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2003'
			THEN COUNT(ClaimNumber) END,0) As Year_4,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2004'
			THEN COUNT(ClaimNumber) END,0) As Year_5,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2005'
			THEN COUNT(ClaimNumber) END,0) As Year_6,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2006'
			THEN COUNT(ClaimNumber) END,0) As Year_7,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2007'
			THEN COUNT(ClaimNumber) END,0) As Year_8,		
	COALESCE(CASE WHEN YEAR(ClosedDate)='2008'
			THEN COUNT(ClaimNumber) END,0) As Year_9,
	COALESCE(CASE WHEN YEAR(ClosedDate)='2009'
			THEN COUNT(ClaimNumber) END,0) As Year_10
END
FROM ClaimTbl
INNER JOIN PoliciesTbl ON Claimtbl.Policy_ID = PoliciesTbl.Policy_ID
INNER JOIN Contracts ON PoliciesTbl.Contract_ID = Contracts.Contract_ID
INNER JOIN vwClosedClaims ON ClaimTbl.ClaimID = vwClosedClaims.ClaimID
INNER JOIN StatusTransactionLogTbl ON ClaimTbl.ClaimID = StatusTransactionLogTbl.ClaimID
WHERE StatusTransactionLogTbl.Status_Id = 1
AND Contracts.InsurerID = 11907
GROUP BY Contract_Name, YEAR(ClosedDate)
ORDER BY Contract_Name


SELECT Contract_Name, SUM(Year_1) as Year_1, SUM (Year_2) as Year_2, SUM(Year_3) as Year_3, SUM(Year_4) as Year_4, SUM(Year_5) as Year_5, SUM(Year_6) as Year_6, SUM(Year_7) as Year_7, SUM(Year_8)  as Year_8, SUM(Year_9) as Year_9, SUM(Year_10) as Year_10, SUM(Year_11) as Year_11  
From @ClosedSameStageTable
GROUP BY Contract_Name

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-23 : 10:58:42

You can only alias the first case level and only after the END statement. I would guess that Case is not what you want to use in this matter, or at least not the way you are using it. If you want all of the fields inserted into the table, you will need a case for each:

CASE WHEN Contract_Name = 'xxxxx' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='1999'
THEN COUNT(ClaimNumber) END,0) END As Year_1,
CASE WHEN Contract_Name = 'xxxxx' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) END As Year_2,
etc...

Wozer
Starting Member

29 Posts

Posted - 2009-03-23 : 11:07:59

Would it be better to do an if Statement then, or would that even work?

For example

IF Contract_Name = 'xxxxx'
BEGIN
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) As Year_1,
COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
THEN COUNT(ClaimNumber) END,0) As Year_2,
COALESCE(CASE WHEN YEAR(ClosedDate)='2002'
THEN COUNT(ClaimNumber) END,0) As Year_3,
COALESCE(CASE WHEN YEAR(ClosedDate)='2003'
THEN COUNT(ClaimNumber) END,0) As Year_4,
COALESCE(CASE WHEN YEAR(ClosedDate)='2004'
THEN COUNT(ClaimNumber) END,0) As Year_5
END

etc.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-23 : 11:29:37

Yes, that would give you the correct result.

Wozer
Starting Member

29 Posts

Posted - 2009-03-23 : 12:07:11

Rick,
Thanks for your help so far. I made the changes to the SP for If statements and I am getting the following errors, which I cannot figure out why they are appearing:

Msg 156, Level 15, State 1, Procedure spxyz, Line 22
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure spxyz, Line 24
Incorrect syntax near the keyword 'COALESCE'.
Msg 156, Level 15, State 1, Procedure spxyz, Line 50
Incorrect syntax near the keyword 'COALESCE'.

The Code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE Procedure [dbo].[spxyz]
AS
DECLARE @ClosedSameStageTable TABLE
( Contract_Name varchar(20),
Year_1 int,
Year_2 int,
Year_3 int,
Year_4 int,
Year_5 int,
Year_6 int,
Year_7 int,
Year_8 int,
Year_9 int,
Year_10 int,
Year_11 int
)

INSERT INTO @ClosedSameStageTable (Contract_Name, Year_1, Year_2, Year_3, Year_4, Year_5, Year_6, Year_7, Year_8, Year_9, Year_10, Year_11)
SELECT Contract_Name,
IF Contract_Name = 'xxxxx'
BEGIN
COALESCE(CASE WHEN YEAR(ClosedDate)='1999'
THEN COUNT(ClaimNumber) END,0) As Year_1,
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) As Year_2,
COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
THEN COUNT(ClaimNumber) END,0) As Year_3,
COALESCE(CASE WHEN YEAR(ClosedDate)='2002'
THEN COUNT(ClaimNumber) END,0) As Year_4,
COALESCE(CASE WHEN YEAR(ClosedDate)='2003'
THEN COUNT(ClaimNumber) END,0) As Year_5,
COALESCE(CASE WHEN YEAR(ClosedDate)='2004'
THEN COUNT(ClaimNumber) END,0) As Year_6,
COALESCE(CASE WHEN YEAR(ClosedDate)='2005'
THEN COUNT(ClaimNumber) END,0) As Year_7,
COALESCE(CASE WHEN YEAR(ClosedDate)='2006'
THEN COUNT(ClaimNumber) END,0) As Year_8,
COALESCE(CASE WHEN YEAR(ClosedDate)='2007'
THEN COUNT(ClaimNumber) END,0) As Year_9,
COALESCE(CASE WHEN YEAR(ClosedDate)='2008'
THEN COUNT(ClaimNumber) END,0) As Year_10,
COALESCE(CASE WHEN YEAR(ClosedDate)='2009'
THEN COUNT(ClaimNumber) END,0) As Year_11
END

ELSE IF Contract_Name = 'xxx'
BEGIN
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) As Year_1,
COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
THEN COUNT(ClaimNumber) END,0) As Year_2,
COALESCE(CASE WHEN YEAR(ClosedDate)='2002'
THEN COUNT(ClaimNumber) END,0) As Year_3,
COALESCE(CASE WHEN YEAR(ClosedDate)='2003'
THEN COUNT(ClaimNumber) END,0) As Year_4,
COALESCE(CASE WHEN YEAR(ClosedDate)='2004'
THEN COUNT(ClaimNumber) END,0) As Year_5,
COALESCE(CASE WHEN YEAR(ClosedDate)='2005'
THEN COUNT(ClaimNumber) END,0) As Year_6,
COALESCE(CASE WHEN YEAR(ClosedDate)='2006'
THEN COUNT(ClaimNumber) END,0) As Year_7,
COALESCE(CASE WHEN YEAR(ClosedDate)='2007'
THEN COUNT(ClaimNumber) END,0) As Year_8,
COALESCE(CASE WHEN YEAR(ClosedDate)='2008'
THEN COUNT(ClaimNumber) END,0) As Year_9,
COALESCE(CASE WHEN YEAR(ClosedDate)='2009'
THEN COUNT(ClaimNumber) END,0) As Year_10
END
FROM ClaimTbl
INNER JOIN PoliciesTbl ON Claimtbl.Policy_ID = PoliciesTbl.Policy_ID
INNER JOIN Contracts ON PoliciesTbl.Contract_ID = Contracts.Contract_ID
INNER JOIN vwClosedClaims ON ClaimTbl.ClaimID = vwClosedClaims.ClaimID
INNER JOIN StatusTransactionLogTbl ON ClaimTbl.ClaimID = StatusTransactionLogTbl.ClaimID
WHERE StatusTransactionLogTbl.Status_Id = 1
AND Contracts.InsurerID = 11907
GROUP BY Contract_Name, YEAR(ClosedDate)
ORDER BY Contract_Name

SELECT Contract_Name, SUM(Year_1) as Year_1, SUM (Year_2) as Year_2, SUM(Year_3) as Year_3, SUM(Year_4) as Year_4, SUM(Year_5) as Year_5, SUM(Year_6) as Year_6, SUM(Year_7) as Year_7, SUM(Year_8) as Year_8, SUM(Year_9) as Year_9, SUM(Year_10) as Year_10, SUM(Year_11) as Year_11
From @ClosedSameStageTable
GROUP BY Contract_Name

I am thinking that the first error has something to do with me doing the insert statement incorrectly but I cannot see where I went wrong. The other error shows up at the first COALESCE after the BEGIN in each If Statement.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-23 : 12:59:16

Sorry, wasn' thinking straight earlier, you can't use the IF statement like this, use the double case as in my earlier post. An IF statement needs to be outside a SQL block, so you can do:


IF (select Contract_Name from Contracts <plus where clause>) = 'xxxxx'
BEGIN
INSERT INTO @ClosedSameStageTable (Contract_Name, Year_1, Year_2, Year_3, Year_4, Year_5, Year_6, Year_7, Year_8, Year_9, Year_10, Year_11)
SELECT Contract_Name,
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) As Year_1,
COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
THEN COUNT(ClaimNumber) END,0) As Year_2,
...
END
ELSE IF (select Contract_Name from Contracts <plus where clause>)  = 'xxx'
BEGIN
INSERT INTO @ClosedSameStageTable (Contract_Name, Year_1, Year_2, Year_3, Year_4, Year_5, Year_6, Year_7, Year_8, Year_9, Year_10, Year_11)
SELECT Contract_Name,
COALESCE(CASE WHEN YEAR(ClosedDate)='2000'
THEN COUNT(ClaimNumber) END,0) As Year_1,
COALESCE(CASE WHEN YEAR(ClosedDate)='2001'
THEN COUNT(ClaimNumber) END,0) As Year_2,
...
END

Wozer
Starting Member

29 Posts

Posted - 2009-03-23 : 15:24:58

I don't think that the double case from your first post will work, because there are multiple different Contracts that I am looking for (I should have clarified that better initially) So if I have something like this

CASE WHEN Contract_Name = 'xxx' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='1999' THEN
COUNT(ClaimNumber) END,0) END As Year_1,
CASE WHEN Contract_Name = 'xxx' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='2000' THEN
COUNT(ClaimNumber) END,0) END As Year_2,
(etc until Year 11)
CASE WHEN Contract_Name = 'yyy' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='1999' THEN
COUNT(ClaimNumber) END,0) END As Year_1,
CASE WHEN Contract_Name = 'yyy' THEN
COALESCE(CASE WHEN YEAR(ClosedDate)='2000' THEN
COUNT(ClaimNumber) END,0) END As Year_2,
(etc until year 11)

Then my select list has more items than the insert list can store (there are 11 columns that can be inserted into but the query is looking to insert many more than that) This is my understanding from the error message I am receiving, even though I am inserting into specific years.

So I took the IF Statement route, I started out with the If statements for each Contract that I need and I got the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In reference to the following line of code:

IF (SELECT Contract_Name FROM Contracts WHERE InsurerID = 11907) = 'xxxx'

Googling and looking around I have found that I would need to use ANY in this statement to make it work correctly, but I cannot figure out how I would go about writing the query to use ANY?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-23 : 16:06:57

I didn't spend much time reading this, but from your post it looks like you can use a pivot Query instead of this.

Look at the BOL PIVOT examples

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Wozer
Starting Member

29 Posts

Posted - 2009-03-24 : 08:33:02

Are there BOL PIVOT examples on this website? If I use the search and type in PIVOT it doesn't find anything. What does the BOL stand for?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources