November 9, 2012 at 10:38 am
Hi,
I'm stuck here. Can anyone help me please it's urgent.
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…..
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_To_OR!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_To_OR!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_to_OR!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_to_OR!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_To_OR!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_To_OR!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_To_OR!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_To_OR!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'
November 9, 2012 at 10:48 am
You have an error in your subquery, but I'm not sure if it's a missing comma or an additional field.
I can't check all the code you posted, I don't know your work environment and I really don't want to check all that unless I'm getting paid for it.
It was a good thing that you pointed out the problem, but it's very little what we can help. In the future, try to use the IFCode available at the left of the posting area.
select Main.*,
(Select TOP 1 (f.ValueStr), --Missing comma in here and incorrect syntax in your WHEN clause.
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
November 9, 2012 at 10:52 am
I'll be up front with you ... I read about 5% of what you wrote ... just too much to wade into.
One piece of advice ... if you are trying to get the top (1) joined record for more than a single row, consider hosting the "top (1) ..." query in a CROSS APPLY or OUTER APPLY. It tends to make things quite a bit easier.
November 9, 2012 at 11:11 am
I would agree bterraberry about using cross apply here.
To directly answer your question I think you need something like this...
select Main.*,
(Select TOP 1
case when f.objectName 'Anesthesia_Type!Anesthesia_Post_Note' = 'TRUE' then f.ValueStr
else 'N/A' end as ValueStr
from BVFindings
FROM where f.sessionid=Main.sessionID ) as ANTYPE
Now in this subquery and ALL the other subqueries you have you are missing one absolutely critical piece when using TOP. You have no order by. In other words, your TOP 1 with no order by is saying just give me 1, I don't care which one. It will not always produce consistent results either. The row returned from top 1 with no order will change from time to time.
The nightmare of a query you posted with dozens and dozens of subqueries all to the same table is reminiscent of an EAV system. You may have other perfectly normalized relational data but this thing is a PITA to work with. Your table effectively holds all different types of data loosely termed "findings" but in order to know what kind of finding you had to add an extra column of objectname.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply