How to check for NULLS using CASE statement and TOP 1 using a subquery

[swenri]

Message Preview
Hi,
I have created a query that is returning three different outputs, expected,fixed and NULL. The problem that I’m facing is when I’m using Temp table #ANTYPE', this is where I am checking for condition A ('Anesthesia_Type!Anesthesia_Post_Note')if met pass A ('Anesthesia_Type!Anesthesia_Post_Note') else B (do N/A) and update in the temp table (N/N when it shows N/A).But in some cases I am getting a NULL and an A and B as expected.I’m trying to check for the above conditions in a sub query as shown below.

select Main.*,
(Select TOP 1 (f.ValueStr)
case when f.objectName 'Anesthesia_Type!Anesthesia_Post_Note' = 'TRUE' then f.ValueStr
else 'N/A' end
from BVFindings f
where f.sessionid=Main.sessionID ) as ANTYPE into #ANTYPE
from @main Main
UPDATE #ANTYPE
set ANTYPE ='N/N'
FROM #ANTYPE
WHERE ANTYPE IS NULL
SELECT * FROM #ANTYPE

I get an error Msg 156, Level 15, State 1, Line 142
Incorrect syntax near the keyword 'case'. How do I get the correct results ? Could any one please let me know how to use case statement with TOP 1 in aubquery or any other method ?

All the code is metioned below….. All help is greatly appreciated. Thank you.

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')


and o.id = object_id(N'tempdb..#final')
)
DROP TABLE #final ;

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')


and o.id = object_id(N'tempdb..#ALLPROC')
)
DROP TABLE #ALLPROC ;

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')


and o.id = object_id(N'tempdb..#ANCOMP')
)
DROP TABLE #ANCOMP ;

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')


and o.id = object_id(N'tempdb..#ANTYPE')
)
DROP TABLE #ANTYPE ;

Declare @Main Table
(SessionID int,
RegistrationAdmissionTime datetime,
LastName varchar(100),
FirstName varchar(100),
MRN int,
AccountNumber int,
DateOfBirth datetime)

insert into @Main
select
mo_times.sessionid,
MO_Times.RegistrationAdmissionTime,
MO_Demographics.LastName,
MO_Demographics.FirstName,
MO_Demographics.MRN,
MO_Demographics.AccountNumber,
MO_Demographics.DateOfBirth --into #Main

FROM
DatamartDB2.dbo.BLSession_Extended BLSession_Extended

JOIN DatamartDB2.dbo.MO_Times MO_Times ON
BLSession_Extended.sessionID = MO_Times.SessionID
and ((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
JOIN DatamartDB2.dbo.MO_Demographics MO_Demographics ON
BLSession_Extended.sessionID = MO_Demographics.SessionID


select Main.*,
(Select top 1
case when Neonate.Vacuum_Extraction = 'true' then 'VC'
when Neonate.Forceps_Delivery='true' then 'FD'
when Neonate.Assisted_Breech_Delivery='true' then 'ABD'
when Neonate.Spontaneous_Breech_Delivery='true' then 'SBD'
when Neonate.Total_Breech_Extraction='true' then 'TBE'
when Neonate.Cesarean_Section='true' then 'CS'
when Neonate.Normal_Vaginal_Delivery='true' then 'NVD'
else 'N/A' end

from DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate as Neonate
where Neonate.sessionid=Main.sessionID ) as ALLVD into #final

from @main Main

UPDATE #final
set ALLVD='N/N'
FROM #final
WHERE ALLVD IS NULL
SELECT * FROM #final



select Main.*,
(Select top 1
case when Reportprocedure.ReportName = 'Dilatation & Curettage Report' then 'D&C'
when Reportprocedure.ReportName = 'Cerclage Report' then 'C&C'
when Reportprocedure.ReportName = 'Intrauterine Transfusion Report' then 'IUT'
when Reportprocedure.ReportName = 'Examination Under Anesthesia' then 'EUA'
when Reportprocedure.ReportName = 'General Operative Procedure' then 'GOP'
when Reportprocedure.ReportName = 'Bilateral Tubal Ligation Report' then 'BTL'
else 'N/A' end
from DatamartdB2.dbo.BVReports as Reportprocedure
where Reportprocedure.sessionID = Main.sessionid) as ALLPROC into #ALLPROC
from @main Main
UPDATE #ALLPROC
set ALLPROC ='N/N'
FROM #ALLPROC
WHERE ALLPROC IS NULL
SELECT * FROM #ALLPROC

select Main.*,
(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 Complications.sessionID = main.sessionid) as ANCOMP into #ANCOMP
from @main Main
UPDATE #ANCOMP
set ANCOMP ='N/N'
FROM #ANCOMP
WHERE ANCOMP IS NULL
SELECT * FROM #ANCOMP

select Main.*,
(Select TOP 1 (f.ValueStr)
case when f.objectName 'Anesthesia_Type!Anesthesia_Post_Note' = 'TRUE'
then f.ValueStr
else 'N/A' end
from BVFindings f
where f.sessionid=Main.sessionID ) as ANTYPE into #ANTYPE
from @main Main
UPDATE #ANTYPE
set ANTYPE ='N/N'
FROM #ANTYPE
WHERE ANTYPE IS NULL
SELECT * FROM #ANTYPE

SELECT
Main.sessionid,
Main.RegistrationAdmissionTime,
Main.LastName,
Main.FirstName,
Main.MRN,
Main.AccountNumber,
Main.DateOfBirth,

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

BVProblems.Problem,
-- Main.#final,
--Main.#ALLPROC,
--Mian.#ANCOMP,

--(Select f.ValueStr
--case when (f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') is not null
-- then (f.objectname = 'Anesthesia_Type!Anesthesia_Post_Note')
-- when (f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note')is null
-- then (f.objectname = 'Anesthesia_Type!Anesthesia_Post_Note')
-- end as ANTYPE, ANTYPE as 'N/A',
--from BVFindings f
--where f.sessionid=Main.sessionID)
--(Select top 1 f.ValueStr
--from BVFindings f
--where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note') as ANTYPE,
--NVD procedure

(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Midwife_Present!Normal_Vaginal_Delivery') as NVDsurgeon,

(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Normal_Vaginal_Delivery') as NVDAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Normal_Vaginal_Delivery') as NVDRES,
--(Select top 1 f.ValueTime
--from BVFindings f
--where f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_TR!Normal_Vaginal_Delivery') as NVDETTOR,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!Cesarean_Section') as CSsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Cesarean_Section') as CSAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cesarean_Section') as CSRES,
--(Select top 1 f.ValueTime
--from BVFindings f
--where f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_TR!Cesarean_Section') as CSETTOR,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!Dilatation_And_Curettage') as DCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Dilatation_And_Curettage') as DCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Dilatation_And_Curettage') as DCRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!Cervical_Cerclage_A') as CCsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Cervical_Cerclage_A') as CCAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cervical_Cerclage_A') as CCRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!Tubal_Ligation') as BTLsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Tubal_Ligation') as BTLAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Tubal_Ligation') as BTLRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!General_Operative_Procedure[#]') as GOPsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!General_Operative_Procedure[#]') as GOPAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!General_Operative_Procedure[#]') as GOPRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!IUT') as IUTsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!IUT') as IUTAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!IUT') as IUTRES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.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=Main.sessionID and f.objectName = 'Surgeon_Present!Birth_Canal_Revision') as EUAsurgeon,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Birth_Canal_Revision') as EUAAN,
(Select top 1 f.ValueStr
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Birth_Canal_Revision') as EUARES,
(Select top 1 f.ValueTime
from BVFindings f
where f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_TR!Birth_Canal_Revision') as EUATIME

FROM
@Main as Main

LEFT OUTER JOIN DatamartDB2.dbo.IPR_Delivery_Report_Neonate_Delivery_Report_Neonate IPR_Delivery_Report_Neonate_Delivery_Report_Neonate ON
Main.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
Main.sessionID = IPR_Anesthetic_Complications_Timed_Ane_Com_Tim.sessionid

LEFT OUTER JOIN DatamartDB2.dbo.BVReports BVReports on
Main.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
Main.sessionID = BVProblems.sessionid and BVProblems.Existence = 'EXISTS'

 

[IanWaterman]

you need comma before case

Select TOP 1 (f.ValueStr),

Ian