SQL Server Forums
Profile | Register | 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)
 CASE & TOP 1 in a sub query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

63 Posts
Posted - 11/09/2012 :  13:11:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/09/2012 :  13:46:03  Show Profile  Reply with Quote
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
Go to Top of Page

swenri
Yak Posting Veteran

63 Posts
Posted - 11/09/2012 :  14:54:25  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/09/2012 :  15:06:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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