Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
swenri
Yak Posting Veteran
72 Posts |
Posted - 2012-11-09 : 13:11:02
|
Hi, This is urgent. Thank you for all the help.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 ? |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-09 : 13:46:03
|
Didn't quite follow the logic you are trying to implement - but the syntax error can be fixed as shown below. ANTYPE will not be null in #ANTYPE table because of the else clause in the case expression in the inner query, so the update statement does not really do anything. But then again, may be ANTYPE is not what you want to name the column from the case expression?SELECT Main.*, ( SELECT TOP 1 f.ValueStr, CASE WHEN f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note' THEN f.ValueStr ELSE 'N/A' END AS ANTYPE FROM BVFindings f WHERE f.sessionid = Main.sessionID ) AS ANTYPE INTO #ANTYPEFROM @main MainUPDATE #ANTYPESET ANTYPE = 'N/N'FROM #ANTYPEWHERE ANTYPE IS NULLSELECT *FROM #ANTYPE |
|
|
swenri
Yak Posting Veteran
72 Posts |
Posted - 2012-11-09 : 14:54:25
|
Thank you for the reply but it gives an error thatMsg 116, Level 16, State 1, Line 157Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.How do I resolve that I can select 2 expressions using a subquery ?Thank you |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-09 : 15:06:06
|
Sorry about that. See below - I am not sure that I understood the logic correctly, so this may still not give you what you are looking for.SELECT Main.*, f.ValueStr, CASE WHEN f.objectName = 'Anesthesia_Type!Anesthesia_Post_Note' THEN f.ValueStr ELSE 'N/A' END AS ANTYPEINTO #ANTYPEFROM @main Main LEFT JOIN BVFindings f ON f.sessionid = Main.sessionID |
|
|
|
|
|
|
|