swenri
Yak Posting Veteran
72 Posts |
Posted - 2012-11-09 : 10:34:37
|
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' endfrom BVFindings fwhere f.sessionid=Main.sessionID ) as ANTYPE into #ANTYPEfrom @main MainUPDATE #ANTYPEset ANTYPE ='N/N'FROM #ANTYPEWHERE ANTYPE IS NULLSELECT * FROM #ANTYPE I get an error Msg 156, Level 15, State 1, Line 142Incorrect 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 @Mainselect 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 =0JOIN 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 Neonatewhere Neonate.sessionid=Main.sessionID ) as ALLVD into #finalfrom @main MainUPDATE #finalset ALLVD='N/N'FROM #finalWHERE ALLVD IS NULLSELECT * FROM #finalselect 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 Reportprocedurewhere Reportprocedure.sessionID = Main.sessionid) as ALLPROC into #ALLPROCfrom @main MainUPDATE #ALLPROCset ALLPROC ='N/N'FROM #ALLPROCWHERE ALLPROC IS NULLSELECT * FROM #ALLPROCselect 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 Complicationswhere Complications.sessionID = main.sessionid) as ANCOMP into #ANCOMPfrom @main MainUPDATE #ANCOMPset ANCOMP ='N/N'FROM #ANCOMPWHERE ANCOMP IS NULLSELECT * FROM #ANCOMPselect Main.*,(Select TOP 1 (f.ValueStr) case when f.objectName 'Anesthesia_Type!Anesthesia_Post_Note' = 'TRUE' then f.ValueStr else 'N/A' endfrom BVFindings fwhere f.sessionid=Main.sessionID ) as ANTYPE into #ANTYPEfrom @main MainUPDATE #ANTYPEset ANTYPE ='N/N'FROM #ANTYPEWHERE ANTYPE IS NULLSELECT * 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.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Midwife_Present!Normal_Vaginal_Delivery') as NVDsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Normal_Vaginal_Delivery') as NVDAN, (Select top 1 f.ValueStrfrom BVFindings fwhere 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_To_OR!Normal_Vaginal_Delivery') as NVDETTOR,(Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as NVDTOD, --CS Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!Cesarean_Section') as CSsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Cesarean_Section') as CSAN, (Select top 1 f.ValueStrfrom BVFindings fwhere 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_To_OR!Cesarean_Section') as CSETTOR,(Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]') as CSTOD,--D&C Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!Dilatation_And_Curettage') as DCsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Dilatation_And_Curettage') as DCAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Dilatation_And_Curettage') as DCRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_to_OR!Dilatation_And_Curettage') as DCTIME,--Cerclage Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!Cervical_Cerclage_A') as CCsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Cervical_Cerclage_A') as CCAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Cervical_Cerclage_A') as CCRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_to_OR!Cervical_Cerclage_A') as CCTIME,--Bi tubal ligation Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!Tubal_Ligation') as BTLsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Tubal_Ligation') as BTLAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Tubal_Ligation') as BTLRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_To_OR!Tubal_Ligation') as BTLTIME,--General Operative Procedure(Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!General_Operative_Procedure[#]') as GOPsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!General_Operative_Procedure[#]') as GOPAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!General_Operative_Procedure[#]') as GOPRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_To_OR!General_Operative_Procedure[#]') as GOPTIME, --IUT Procedure (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!IUT') as IUTsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!IUT') as IUTAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!IUT') as IUTRES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_To_OR!IUT') as IUTTIME, --Exam under anesthesia Procedure (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Surgeon_Present!Birth_Canal_Revision') as EUAsurgeon, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesiologist_Present!Birth_Canal_Revision') as EUAAN, (Select top 1 f.ValueStrfrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Anesthesia_Resident_Present!Birth_Canal_Revision') as EUARES, (Select top 1 f.ValueTimefrom BVFindings fwhere f.sessionid=Main.sessionID and f.objectName = 'Entry_Time_To_OR!Birth_Canal_Revision') as EUATIME FROM @Main as MainLEFT 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 ONMain.sessionID = IPR_Anesthetic_Complications_Timed_Ane_Com_Tim.sessionidLEFT OUTER JOIN DatamartDB2.dbo.BVReports BVReports onMain.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 onMain.sessionID = BVProblems.sessionid and BVProblems.Existence = 'EXISTS' |
|