Help with Nested Case Statements

Hi,

I am trying to write a query which is something like this :

CASE WHEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

But this is obviously not correct because the WHEN statements do not have a THEN statement - but I dont have anything to do in THEN statement becuase the nested CASE statements take care of everything. Can anybody please help me modify the query so that it works?

Thanks !!

It is not clear what you are attempting to accomplish. Perhaps if you were to present the entire concept, and the entire query, we might be better able to help you.|||

SELECT CASE crp.Title_Code WHEN crp1.Title_Code
THEN ar.Description + '|' + ar1.Description,
WHEN crp2.Title_Code
THEN ar.Description + '|' + ar2.Description,
WHEN crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
UNION
SELECT CASE crp1.Title_Code WHEN crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description ,
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
UNION
SELECT CASE crp2.Title_Code WHEN crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

Of course you need to incorporate your JOINS.

Just my twist on it,

Adamus

|||

Agreed. Can you at least make it clear if you are trying to get one value back, or three? Each of the WHEN clauses needs a boolean expression to determin if it is used. So if you want one value, it should be something like

CASE WHEN <boolean condition>
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN <boolean condition>
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN <boolean condition>
THEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

|||

I think I need to give the complete description here..This is the whole query - I modified it to get it to working..But my query returns only one record whereas it should return 4 different records if Reason_Code_ID1,Reason_Code_ID2,Reason_Code_ID3,Reason_Code_ID4 have different values for look up field of Title_Code and should concatenate the description for ones that have same Title_Code value.

Select CASE WHEN ar1.Description is NOT NULL AND ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
WHEN ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
WHEN ar3.Description is not null THEN
CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END
END AS Reason_Code_Description,
crp.CRP_Score_Reason_Code_ID,
crp.Title_Code
From ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code crp
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm (nolock)
JOIN CCR..ADF_Reference ar (nolock)
ON arm.Lookup_ID = ar.Lookup_ID AND ar.Language = 'F'
ON arm.Bureau_Code_ID = '1'
AND arm.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar.Code = crp.CRP_Score_Reason_Code_ID
AND arm.Segment = 'CRP'
ON (crp.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
LEFT OUTER JOIN CRP_Score_Reason_Code crp1
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm1 (nolock)
JOIN CCR..ADF_Reference ar1 (nolock)
ON arm1.Lookup_ID = ar1.Lookup_ID AND ar1.Language = 'F'
ON arm1.Bureau_Code_ID = '1'
AND arm1.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar1.Code = crp1.CRP_Score_Reason_Code_ID
AND arm1.Segment = 'CRP'
ON (crp1.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
LEFT OUTER JOIN CRP_Score_Reason_Code crp2
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm2 (nolock)
JOIN CCR..ADF_Reference ar2 (nolock)
ON arm2.Lookup_ID = ar2.Lookup_ID AND ar2.Language = 'F'
ON arm2.Bureau_Code_ID = '1'
AND arm2.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar2.Code = crp2.CRP_Score_Reason_Code_ID
AND arm2.Segment = 'CRP'
ON (crp2.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
LEFT OUTER JOIN CRP_Score_Reason_Code crp3
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm3 (nolock)
JOIN CCR..ADF_Reference ar3 (nolock)
ON arm3.Lookup_ID = ar3.Lookup_ID AND ar3.Language = 'F'
ON arm3.Bureau_Code_ID = '1'
AND arm3.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar3.Code = crp3.CRP_Score_Reason_Code_ID
AND arm3.Segment = 'CRP'
ON (crp3.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

Please help.

Thanks!

|||

That is quite a SQL statement :) Can you give us some sample DDL and INSERT statements to make this more clear (and more simple) and sample results (expecting 2 rows, not 4 :)

Thanks

|||

There must be a more difficult way to code this. :)

You might be able to filter this query down in the WHERE clause instead of having a multitude of JOINS and SELECT CASE

What exactly is the goal of this query?

Adamus

|||

SELECT rc.Reason_Code_Description FROM
ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code rc ON
(rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

This was the initail query that returnd following results :

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD|Total monthly payments.||||E|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

I had to modify the query so that if Title_code (D,E,K,L) in above example are same , like below

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD| Age of oldest retail account.||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

then the data segments should be concatenated into one to give this result:

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

What my modified query returns is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|

And what I would like to return is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .|Total balance||||K|
CBSSD|Number of sales.||||L|

I know its a bit confusing and complex and that is why I am here ..I am totally confused...:)

Please help.

Thanks!

|||

Although the below example isn't a complete solution to your problem [as I don't have either the table definitions or the patience to unpick your CASE statements :) ], it should help to start you off thinking of solutions along similar lines.

The example requires SQL Server 2005.

Chris

DECLARE @.MyTable TABLE ([Desc] VARCHAR(100) NULL, [Code] CHAR(1))
INSERT INTO @.MyTable
SELECT 'Number of purchases in the previous 12 months.', 'D' UNION ALL
SELECT 'Age of oldest retail account', 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT 'Total high credit.', 'K' UNION ALL
SELECT NULL, 'K' UNION ALL
SELECT 'Total balance', 'K' UNION ALL
SELECT NULL,'K' UNION ALL
SELECT 'Number of sales.', 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L'

DECLARE @.MyCodeTable TABLE ([Code] CHAR(1))
INSERT INTO @.MyCodeTable
SELECT 'D' UNION
SELECT 'K' UNION
SELECT 'L'

SELECT 'CBSSD|'
+ REPLACE(
REPLACE(
(SELECT REPLACE(ISNULL(mt.[Desc], '') + '|', ' ', '~') AS [data()]
FROM @.MyTable mt
WHERE mt.[Code] = mct.[Code]
ORDER BY 1 DESC
FOR XML PATH('')), ' ', '')
, '~', ' ')
+ mct.[Code] + '|' AS [String]
FROM @.MyCodeTable mct
/*Output
CBSSD|Number of purchases in the previous 12 months.|Age of oldest retail account|||D|
CBSSD|Total high credit.|Total balance|||K|
CBSSD|Number of sales.||||L|
*/