Using CASE Stmt - Embedding CASE Stmt? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using CASE Stmt - Embedding CASE Stmt?

Author

Topic

CharlesPatrickO
Starting Member

1 Post

Posted - 2008-10-28 : 00:54:33

See my work-in-process SQL below.
When the CMN_Error_MST_ID = 85, I need to check the LoanEff_DT field. If Loan Effective Date is not present, I need to display the words "Deficient". If it is present, then I display it as is. The FLAG field -> CMN_Error_MST_ID tells me there is something wrong (when it is equal to 85 then the Loan Effective Date is invalid). My SQL below is incorrect but I have explored some ideas. I know to display the date field as text, I need to change its type. I have to perform this same logic when the FLAG field contains different values which relate to fields other than Loan Effective Date but the syntax will be the same. Can someone help me ? I know my CASE syntax is incomplete and won't get the job done.

CASE WHEN DL.CMN_Error_MST_ID = 85,
CASE WHEN LD.LoanEff_DT = ‘ ‘
THEN CAST (LD.LoanEff_DT,'Deficient')
ELSE END as LoanEffDate

CASE WHEN DL.CMN_Error_MST_ID = 82,
CASE WHEN LD.LoanBal = ‘ ‘
THEN CAST (LD.LoanBal,'Deficient')
ELSE END as LoanBalance

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-28 : 01:15:06

SELECT
CASE WHEN DL.CMN_Error_MST_ID = 85,
CASE WHEN isnull(LD.LoanEff_DT,'') = ‘‘ THEN 'Deficient'
ELSE LD.LoanEff_DT END as LoanEffDate
END
ELSE ???
FROM Table1 DL JOIN Table2 LD...

Please go through SQL Server books online to learn the various constructs of T-SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 01:29:29

no need of , at end of CASE WHEN

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-28 : 05:54:18

Could be

SELECT
CASE WHEN (DL.CMN_Error_MST_ID = 85 AND LD.LoanEff_DT = '')
     THEN 'Deficient'
     ELSE LD.LoanEff_DT END AS LoanEffDate,
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 05:58:38

quote:

Originally posted by bjoerns

Could be

SELECT
CASE WHEN (DL.CMN_Error_MST_ID = 85 AND LD.LoanEff_DT = '')
     THEN 'Deficient'
     ELSE LD.LoanEff_DT END AS LoanEffDate,
...

Value returned by all conditions of CASE whould be of same datatype. So if LD.LoanEff_DT is datetime you might have to CAST it to varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources