Solved

SQL subquery error - MS SQL server 2005

Posted on 2011-02-24
9
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

615 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