Solved

SQL subquery error - MS SQL server 2005

Posted on 2011-02-24
9
327 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now