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 2005 Forums
 Transact-SQL (2005)
 New to SQL: Subquery Error Help !!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sushant17284
Starting Member

12 Posts

Posted - 01/27/2010 :  03:10:54  Show Profile  Reply with Quote
I have a problem with an SQL server query.
The error that I am getting is the subquery return more than one value.This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The following is my query :
I have a temporary report table called ReportsAdditionalComment
I am doing an update in this temp table and selecting additional comments.Now the problem is when the following statement runs inside the stored procedure it returns the above error of subquery returned more than one value.Obviously, I can make out since I am setting out into one variable , it will not accept if the inner query returns more than one row.But I don't know how to modify the SET part of this query so that it can return more than one Row.I need to have multiple comments returned and set in ADD_COMMENTS.


UPDATE #TEMP_ReportsAdditionalComment
SET ADD_COMMENTS = (SELECT	RE.REMARKS   	
         		FROM	ReviewEvent	RE	
			WHERE	RE.PROJECT_ID = @PROJ_ID
			AND	RE.REPOINT_ID = @REPOINT_ID
			AND	RE.REVIEW_DATE = @REVIEW_DATE
			)

The type of ADD_COMMENTS is VARCHAR(1000).
I hope I have explained it clearly.I seemed to be stuck on this for the last two days and since I am a novice in SQL , I really don't know what could be the solution.By the way the inner SQL query executes perfectly and returns two rows but I don't know how to set up multiple rows in ADD_COMMENTS .Please Help.

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  03:15:18  Show Profile  Reply with Quote
Do you want to concatenate the multiple results returned from the inner Sub-Query? Or the single Remark from the most recent Review Date? or something else?
Go to Top of Page

sushant17284
Starting Member

12 Posts

Posted - 01/27/2010 :  03:43:54  Show Profile  Reply with Quote
Well It would be fine with me to concatenate the multiple results returned from the subquery.But,In an ideal situation I would like to know ...Is there any way of setting multiple rows into single variable ADD_COMMENTS ??.But at this point of time, concatenating the multiple results should be fine with me as I will move a little forward.Thanks for your reply Kristen.
Go to Top of Page

sushant17284
Starting Member

12 Posts

Posted - 01/27/2010 :  03:49:04  Show Profile  Reply with Quote
Is it possible to concatenate the multiple rows and add a delimiter in SQL so that it would be an easier task to separate them at the client side again ??
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  04:34:51  Show Profile  Reply with Quote
"Is there any way of setting multiple rows into single variable ADD_COMMENTS"

Can you explain what you mean by this - i.e. how that is different to concatenating them with a delimiter?

Normally you would have a Parent Table (Projects) and a child table (ProjectRemarks) where there is a one-to-many relationship between the data.

Concatenating them, with a delimiter, is normally just for "presentation" purposes.

Edited by - Kristen on 01/27/2010 04:35:25
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.09 seconds. Powered By: Snitz Forums 2000