Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to use Check for NULLS using TOP 1 and CASE i
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

72 Posts

Posted - 11/09/2012 :  10:34:37  Show Profile  Reply with Quote
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_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'

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 11/09/2012 :  15:19:21  Show Profile  Reply with Quote
Dupe:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180525


Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000