CASE & TOP 1 in a sub query - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CASE & TOP 1 in a sub query

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' 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 ?

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 #ANTYPE
FROM   @main Main

UPDATE #ANTYPE
SET    ANTYPE = 'N/N'
FROM   #ANTYPE
WHERE  ANTYPE IS NULL

SELECT *
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 that

Msg 116, Level 16, State 1, Line 157
Only 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 ANTYPE
INTO #ANTYPE
FROM   @main Main
	LEFT JOIN BVFindings     f
		ON f.sessionid = Main.sessionID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources