Help with NULLs IN CASE statement

[swenri]

I need a query that will check the expression and if true puts the expression in the alias.
If the expression is not true put text ‘N/A’ in the alias.
Last because of null and blank values I need the alias to have a text value of ‘N/N’ if the two above conditions are not met. This is in CASE statement. Can anybody help with this please ? Thank you

********************************************************************************************
SELECT
mo_times.sessionid,
MO_Times.RegistrationAdmissionTime,
MO_Demographics.LastName,
MO_Demographics.FirstName,
MO_Demographics.MRN,
MO_Demographics.AccountNumber,
MO_Demographics.DateOfBirth,

BVReports.ReportName,
(BLUser_Names.firstname) as PATNFIRSTNAME,
BLUser_Names.surname as PATNLASTNAME,
BLUser_Names.title as PATNTITLE,

BVProblems.Problem,

(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
--AND Neonate.Vacuum_Extraction IS NOT NULL
--OR Neonate.Forceps_Delivery IS NOT NULL
--OR Neonate.Normal_Vaginal_Delivery IS NOT NULL
) as ALLVD,



--(Select top 1 f.ValueStr
--from BVFindings f
--where f.sessionid=BLSession_Extended.sessionID
--and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE,


Case when BVReports.ReportName = 'Dilatation & Curettage Report' then 'D&C'
when BVReports.ReportName = 'Cerclage Report' then 'C&C'
when BVReports.ReportName = 'Intrauterine Transfusion Report' then 'IUT'
when BVReports.ReportName = 'Examination Under Anesthesia' then 'EUA'
when BVReports.ReportName = 'General Operative Procedure' then 'GOP'
when BVReports.ReportName = 'Bilateral Tubal Ligation Report' then 'BTL'
else NULL end as ALLPRO,

(Select top 1
case when Complications.Difficult_Intubation='true' then 'DI'
when Complications.Aspiration='true' then 'ASP'
when Complications.Malignant_Hyperthermia='true' then 'MH'
when Complications.Seizures= 'true' then 'S'
when Complications.Headache_F='true' then 'H'
when Complications.Paresthesia='true' then 'P'
when Complications.Neurologic_Deficit='true' then 'ND'
when Complications.Hypotension='true' then 'H'
when Complications.Sinus_Bradycardia='true' then 'SB'
when Complications.Arrhythmia='true' then 'ARR'
when Complications.Respiratory_Depression='true' then 'RD'
when Complications.Urinary_Retention='true' then 'UR'
when Complications.Anaphylactic_Shock='true' then 'ASA'
when Complications.Bronchospasm='true' then 'BRO'
when Complications.Epidural_Abscess='true' then 'EA'
when Complications.Fetal_Neonatal_Depression='true' then 'FND'
when Complications.Other_Anesthesia_Complication='true' then 'OAC'
else 'N/A' end
from DatamartdB2.dbo.IPR_Anesthetic_Complications_Timed_Ane_Com_Tim as Complications
where BLSession_Extended.sessionID = Complications.sessionid) as ANCOMP,

(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID
and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE,
--NVD procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Midwife_Present!Normal_Vaginal_Delivery') as NVDsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Normal_Vaginal_Delivery') as NVDAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Normal_Vaginal_Delivery') as NVDRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as NVDTOD,
--CS Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cesarean_Section') as CSsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cesarean_Section') as CSAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cesarean_Section') as CSRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as CSTOD,
--D&C Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Dilatation_And_Curettage') as DCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Dilatation_And_Curettage') as DCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Dilatation_And_Curettage') as DCRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_tR!Dilatation_And_Curettage') as DCTIME,
--Cerclage Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Cervical_Cerclage_A') as CCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Cervical_Cerclage_A') as CCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cervical_Cerclage_A') as CCRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_tR!Cervical_Cerclage_A') as CCTIME,
--Bi tubal ligation Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Tubal_Ligation') as BTLsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Tubal_Ligation') as BTLAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Tubal_Ligation') as BTLRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_TR!Tubal_Ligation') as BTLTIME,
--General Operative Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!General_Operative_Procedure[#]') as GOPsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!General_Operative_Procedure[#]') as GOPAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!General_Operative_Procedure[#]') as GOPRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_TR!General_Operative_Procedure[#]') as GOPTIME,
--IUT Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!IUT') as IUTsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!IUT') as IUTAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!IUT') as IUTRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_TR!IUT') as IUTTIME,
--Exam under anesthesia Procedure
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Surgeon_Present!Birth_Canal_Revision') as EUAsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesiologist_Present!Birth_Canal_Revision') as EUAAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Anesthesia_Resident_Present!Birth_Canal_Revision') as EUARES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=BLSession_Extended.sessionID and f.objectName = 'Entry_Time_TR!Birth_Canal_Revision') as EUATIME

FROM
DatamartDB2.dbo.BLSession_Extended BLSession_Extended

JOIN DatamartDB2.dbo.MO_Times MO_Times ON
BLSession_Extended.sessionID = MO_Times.SessionID

JOIN DatamartDB2.dbo.MO_Demographics MO_Demographics ON
BLSession_Extended.sessionID = MO_Demographics.SessionID

--LEFT OUTER JOIN DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate IPR_Delivery_Report_Neonate_Delivery_Report_Neonate ON
--BLSession_Extended.sessionID = IPR_Delivery_Report_Neonate_Delivery_Report_Neonate.sessionid

--LEFT OUTER JOIN DatamartdB2.dbo.IPR_Anesthetic_Complications_Timed_Ane_Com_Tim IPR_Anesthetic_Complications_Timed_Ane_Com_Tim ON
--BLSession_Extended.sessionID = IPR_Anesthetic_Complications_Timed_Ane_Com_Tim.sessionid

LEFT OUTER JOIN DatamartDB2.dbo.BVReports BVReports on
BLSession_Extended.sessionID = BVReports.sessionid and BVReports.ReportName = 'Post Anesthesia Transfer Note'

LEFT OUTER JOIN DatamartDB2.dbo.BLUser_Names BLUser_Names ON BVReports.SignerId=BLUser_Names.userID

LEFT OUTER JOIN DatamartDB2.dbo.BVProblems BVProblems on
BLSession_Extended.sessionID = BVProblems.sessionid and BVProblems.Existence = 'EXISTS'

WHERE
((MO_Times.RegistrationAdmissionTime >={ts '2012-06-11 00:00:00'}) AND
(MO_Times.RegistrationAdmissionTime <{ts '2012-06-12 00:00:00'})) and
BLSession_Extended.FacilityID =0

 

[bborissov]

What alias?

Why not just JOIN BVFindings or even a derived table from it.
I see that you didn't care about what is the value of [ValueTime] of your subqueries.
You just get the first.

Borislav Borissov
VFP9 SP2, SQL Server