user3697670 user3697670 - 5 months ago 17
MySQL Question

Issue with NULL value in nested case

Long story short i need to return a varchar value if stored value is null but eithers bring me a NULL value or varchar value here is the code.

in the table i have some fields null and some with data

CASE WHEN DS.DBIRTH+DS.MBIRTH+DS.YBIRTH =
'' THEN CONVERT(VARCHAR(10),PT.CLIENTDBIRTHDATE,111)
ELSE 'INPUT DATE OF BIRTH'
CASE WHEN PT.CLIENTBIRTHDATE IS NULL THEN ''
ELSE '1800-01-01'
END
END AS BFIELD


this brings me something like this

NULL
1917/05/02
NULL
1923/02/02
1967/01/05
NULL


but i need something like this

01/01/1800
1917/05/02
01/01/1800
1923/02/02
1967/01/05
01/01/1800


sorry for the ultra noob question

Answer

Your current case statement looks off. I imagine what you want to do is wrap the result in coalesce:

COALESCE(CASE 
             WHEN DS.DBIRTH+DS.MBIRTH+DS.YBIRTH = '' 
             THEN CONVERT(VARCHAR(10),PT.CLIENTDBIRTHDATE,111)
             ELSE 'INPUT DATE OF BIRTH'
         END, '01/01/1800') AS BFIELD
Comments