SQL subquery error - MS SQL server 2005

Dear experts,

I have the below SQL code on Microsoft SQL server management studio 2005 and it compiles ok but when i try to run i get the following error message:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I think the issue might be somewhere in the WHERE clause but cant work out what it is.
any ideas?


UPDATE test.dbo.salesanalysisactual 
SET    actualvalue = Isnull(actualvalue, 0) + (SELECT 
                     SUM(( ( t2.ord_gross_item_value ) 
                                  / 100 ) / ( 
       t1.ord_currency_rate / 
       100000000 
       ) 
       ) 
       FROM   iconinvoices.dbo.ord_invitems t2 
       INNER JOIN iconinvoices.dbo.ord_invoices t1 
       ON t2.ord_invoice_number = 
       t1.ord_invoice_number 
       INNER JOIN test.dbo.[@Calendar] t5 
       ON t1.ord_tax_point_date >= t5.u_startdate 
       AND t1.ord_tax_point_date <= t5.u_enddate 
       INNER JOIN iconinvoices.dbo.sal_address t3 
       ON t1.sal_branch = t3.sal_branch 
       AND t1.sal_account = t3.sal_account 
       WHERE  ( t1.ord_document_type = 'S' 
       OR t1.ord_document_type = 'C' 
       OR t1.ord_document_type = 'N' ) 
       AND t2.ord_item_kind = 'S' 
       AND t1.ord_tax_point_date = 
       salesanalysisactual.taxdate 
       AND t5.u_year = salesanalysisactual.YEAR 
       AND t5.u_period = salesanalysisactual.period 
       AND t5.u_week = salesanalysisactual.week 
       AND t1.salesman_code = 
       salesanalysisactual.repcode 
       AND t1.area_code = salesanalysisactual.areacode 
       AND t1.sal_account = 
       salesanalysisactual.customer 
       AND ( t1.sal_branch + t3.sal_address_1 ) = 
       salesanalysisactual.location 
       AND t2.ord_item_code = 
       salesanalysisactual.partcode 
       GROUP  BY t1.ord_tax_point_date, 
       t5.u_year, 
       t5.u_period, 
       t5.u_week, 
       t1.salesman_code, 
       t1.area_code, 
       t1.sal_account, 
       t2.ord_item_code, 
       t1.ord_currency_rate, 
       ( t1.sal_branch + t3.sal_address_1 ))

Open in new window

kevin1983Asked:
Who is Participating?
 
raulggonzalezConnect With a Mentor Commented:
Hi,

Just remove the group by clause in the subselect and you will get 1 amount for sure, what I don't know it's if the amount is what you're looking for...


cheers

UPDATE test.dbo.salesanalysisactual 
SET    actualvalue = Isnull(actualvalue, 0) + 

			(SELECT SUM ( ( 
							( t2.ord_gross_item_value ) 
                                  / 100 ) 
                                  / 
                            (t1.ord_currency_rate 
								/ 
							100000000 ) 
						) 
				FROM   iconinvoices.dbo.ord_invitems t2 
					INNER JOIN iconinvoices.dbo.ord_invoices t1 
						ON t2.ord_invoice_number = t1.ord_invoice_number 
					INNER JOIN test.dbo.[@Calendar] t5 
						ON t1.ord_tax_point_date >= t5.u_startdate 
							AND t1.ord_tax_point_date <= t5.u_enddate 
					INNER JOIN iconinvoices.dbo.sal_address t3 
						ON t1.sal_branch = t3.sal_branch 
							AND t1.sal_account = t3.sal_account 
				WHERE  ( t1.ord_document_type = 'S' 
					OR t1.ord_document_type = 'C' 
					OR t1.ord_document_type = 'N' ) 
					AND t2.ord_item_kind = 'S' 
					AND t1.ord_tax_point_date = salesanalysisactual.taxdate 
					AND t5.u_year = salesanalysisactual.YEAR 
					AND t5.u_period = salesanalysisactual.period 
					AND t5.u_week = salesanalysisactual.week 
					AND t1.salesman_code = salesanalysisactual.repcode 
					AND t1.area_code = salesanalysisactual.areacode 
					AND t1.sal_account = salesanalysisactual.customer 
					AND ( t1.sal_branch + t3.sal_address_1 ) = salesanalysisactual.location 
					AND t2.ord_item_code = salesanalysisactual.partcode 
       --GROUP  BY t1.ord_tax_point_date, 
       --t5.u_year, 
       --t5.u_period, 
       --t5.u_week, 
       --t1.salesman_code, 
       --t1.area_code, 
       --t1.sal_account, 
      -- t2.ord_item_code, 
       --t1.ord_currency_rate, 
       --( t1.sal_branch + t3.sal_address_1 )
       
       )

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Your subquery is returning more than one value
+ (SELECT
                     SUM(( ( t2.ord_gross_item_value .....

In this case, it should return only one value. Then only it can sum and update the table column

Check the query

0
 
Atdhe NuhiuConnect With a Mentor Commented:
The where clause should be such that only one record is returned so there is a single value to be assigned to actualvalue for each record

Run the subquery in isoaltion to determine why more than one record is being returned
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
jmnfConnect With a Mentor Commented:
you can also try a

SELECT TOP 1 somefield

together with an

ORDER BY someotherfield

on the subquery
0
 
kevin1983Author Commented:
raulggonzalez: yes what you suggested about removing the group by clause seems to work
0
 
kevin1983Author Commented:
jmnf: ok ill try that
0
 
raulggonzalezCommented:
Hi,

It's true that my solution will work, and if you say TOP 1, obviously you will have just one record and it will work too.

but the question is which is the value you're looking for. I agree completely with andycrofts and you should isolate the subquery first using fixed values to verify that the result is what you want and later use it in the UPDATE statement.

Cheers
0
 
kevin1983Author Commented:
thanks all for you help, ive commented out the group by's for now and will check the numbers and will look at running the subquery on its own.
0
 
Atdhe NuhiuCommented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.