Need urgent help please

[swenri]

I need help with the CASE query please.

My output can not have a null or blank value. It has to have a value like ‘NVD’ or ‘N/A’ anything except NULL. For example :
(Select top 1
case when ISNULL (Neonate.Vacuum_Extraction ,'0' ) = 'true' then 'VC'
when ISNULL (Neonate.Forceps_Delivery,'0' )='true' then 'FD'
when ISNULL (Neonate.Assisted_Breech_Delivery,'0' )='true' then 'ABD'
when ISNULL (Neonate.Spontaneous_Breech_Delivery,'0')='true' then 'SBD'
when ISNULL (Neonate.Total_Breech_Extraction,'0')='true' then 'TBE'
when ISNULL (Neonate.Cesarean_Section,'0' )='true' then 'CS'
when ISNULL (Neonate.Normal_Vaginal_Delivery,'0' )='true' then 'NVD'
else 'N/A' end
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where BLSession_Extended.sessionID = Neonate.sessionid ) as ALLVD,

This output will give me ‘NVD’, ‘N/A’ and NULL. This is necessary as my output should go to Crystal Reports which does not recognize a NULL value that I can work with. When I try and string fields together (concatenate) with a NULL value in the middle, it drops everything after the NULL field.

Can any body resolve the issue please. Appreciate all the help.

 

[gmmastros]

I think your problem is that this is a subquery part of a larger query. Furthermore, that large (outer) query is left joined to table(s) that may not have this data.

Anyway.... you can accommodate this by adding another isnull check around the whole thing.

[!]IsNull([/!](Select top 1
case when ISNULL (Neonate.Vacuum_Extraction ,'0' ) = 'true' then 'VC' 
when ISNULL (Neonate.Forceps_Delivery,'0' )='true' then 'FD' 
when ISNULL (Neonate.Assisted_Breech_Delivery,'0' )='true' then 'ABD' 
when ISNULL (Neonate.Spontaneous_Breech_Delivery,'0')='true' then 'SBD' 
when ISNULL (Neonate.Total_Breech_Extraction,'0')='true' then 'TBE' 
when ISNULL (Neonate.Cesarean_Section,'0' )='true' then 'CS' 
when ISNULL (Neonate.Normal_Vaginal_Delivery,'0' )='true' then 'NVD'
else 'N/A' end
from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate 
where BLSession_Extended.sessionID = Neonate.sessionid )[!], 'N/A')[/!] as ALLVD,

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom