Solved

SQL subquery error - MS SQL server 2005

Posted on 2011-02-24
9
329 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:kevin1983
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34971028
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
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 166 total points
ID: 34971035
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
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 167 total points
ID: 34971066
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 4

Assisted Solution

by:jmnf
jmnf earned 167 total points
ID: 34971295
you can also try a

SELECT TOP 1 somefield

together with an

ORDER BY someotherfield

on the subquery
0
 

Author Comment

by:kevin1983
ID: 34971905
raulggonzalez: yes what you suggested about removing the group by clause seems to work
0
 

Author Comment

by:kevin1983
ID: 34972090
jmnf: ok ill try that
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34972205
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
 

Author Closing Comment

by:kevin1983
ID: 34997649
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
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34997678
Thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question