Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL subquery error - MS SQL server 2005

Posted on 2011-02-24
9
Medium Priority
?
336 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 664 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 668 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Assisted Solution

by:jmnf
jmnf earned 668 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

926 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