question

jayakrishnan.r avatar image
jayakrishnan.r asked

Sql Query Rearranging

Can any one help me to rearrange this Query. This query return an error. the error is "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." SELECT ProCode , ProName , ProStdPkt , ProROL , ManName , ProMaxQty , ( SELECT SUM(dbo.tblOutletStockReturnToWareDetails.retQty) AS RetnQty FROM dbo.tblOutletStockReturnToWareDetails INNER JOIN dbo.tblOutletStockReturnToWareMaster ON dbo.tblOutletStockReturnToWareDetails.refNo = dbo.tblOutletStockReturnToWareMaster.refNo WHERE ( dbo.tblOutletStockReturnToWareMaster.Status = 'W' ) AND ( dbo.tblOutletStockReturnToWareDetails.ProCode = A.Procode ) ) AS RetnQty , ( SELECT SUM(dbo.tblSubstoreStockReturnToWareDetails.retQty) AS RetnQty FROM dbo.tblSubstoreStockReturnToWareDetails INNER JOIN dbo.tblSubstoreStockReturntoWareMaster ON dbo.tblSubstoreStockReturnToWareDetails.refNo = dbo.tblSubstoreStockReturntoWareMaster.refNo WHERE ( dbo.tblSubstoreStockReturntoWareMaster.Status = 'W' ) AND ( dbo.tblSubstoreStockReturnToWareDetails.ProCode = A.Procode ) ) AS SubRetnQty , ( SELECT SUM(PDBalanceStock) FROM tblPurchaseDetailsTemp WHERE ProCode = A.Procode ) AS PURCHASEPIPELINEQTY1 , ( SELECT SUM(PDBalanceStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.Procode AND PMSts = 'W' ) AS PURCHASEPIPELINEQTY2 , ( SELECT SUM(PDBalanceStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.Procode AND PMSts = 'A' ) AS BALANCEQTY , ( SELECT SUM(PDStock) FROM VIEWPURORDERPURCHASEQTY WHERE ProCode = A.ProCode ) AS TOTALPURCHASE , ( SELECT SUM(Qty) FROM tblSalesTable WHERE ProCode = A.ProCode ) AS COUNTERSTOCK , ( SELECT SUM(BalQty) FROM tblItemIssueDetails WHERE ProCode = A.ProCode AND Sts = 'W' ) AS COUNTERSTOCKPIPELINE , ( SELECT SUM(EdtQty) FROM tblItemIssueDetailsSubStore WHERE ProCode = A.ProCode AND Sts = 'A' ) AS SUBSTORESTOCK , ( SELECT SUM(EdtQty) FROM tblItemIssueDetailsSubStore WHERE ProCode = A.ProCode AND Sts = 'W' ) AS SUBSTORESTOCKPIPELINE , ( SELECT SUM(Qty) FROM AViewOrderROL WHERE ProCode = A.ProCode AND issueDate >= '2013-04-18 9:54:47 AM' AND issueDate <= '2013-05-18 9:55:02 AM' ) AS SALESQTY , ( SELECT TOP 1 [PDPRate] FROM tblPurchaseDetails WHERE ProCode = A.ProCode ORDER BY PDID DESC ) AS PRATE , ( SELECT SUM(Qty) FROM AViewOrderROLSubstore WHERE ProCode = A.ProCode AND issueDate >= '2013-04-18 9:54:47 AM' AND issueDate <= '2013-05-18 9:55:02 AM' ) AS COUNTERSALESQTY , ( SELECT SUM(CASE WHEN tblBillmaster.Series = 'Cash' THEN tblBilLDetails.Qty ELSE 0 END) AS CashSales , SUM(CASE WHEN tblBillmaster.Series = 'Credit' THEN tblBilLDetails.Qty ELSE 0 END) AS CreditSales , SUM(tblBillDetails.Qty) AS TotalSales FROM tblBillMaster INNER JOIN dbo.tblBillDetails ON dbo.tblBillMaster.BillDispNumber = dbo.tblBillDetails.BillNo WHERE ( dbo.tblBillDetails.Pcode = A.Procode ) ) FROM VIEWPURCHASEORDERALL AS A WHERE SupName = 'AAA' AND ProStatus = 'Active' ORDER BY ProName
sql-server-2005sub-querysubquery
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have fixed it up....... Thank you guys....
0 Likes 0 ·
tomgough79 avatar image
tomgough79 answered
Your problem appears to be in your last select statement as it returns 3 columns - when you are using a select statement as an expression, it must only return 1 column and 1 row. You could factor it as 3 separate select statements. I would, however, strongly advise re-writing the query to explicitly use joins, rather than select statements as expressions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oh crud. Missed that. Good catch.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
It's not one we can fix by simply looking at the query. The pattern you're following requires that each of those sub-queries you're using return only one value. You need to look at each one to determine which of them is returning more than one value. That's the one that needs to be fixed. By the way, in most cases, you would be much better served changing everything from all these correlated queries into a series of JOINs. Also, since so many of these correlated queries are doing aggregates, you might be better served turning some of them into materialized views and pre-aggregating that data.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
This looks like a follow-on from an earlier question - http://ask.sqlservercentral.com/questions/102734/how-to-reduce-the-execution-time-of-sql-query.html - where my advice wasn't quite followed correctly! Rather than doing what I suggested - putting those last three items into a separate JOIN table, you've put them into a separate SUBQUERY. That's not going to work. Go back, read my answer to that question again, and have another go.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The other thing is, as @Grant Fritchey mentioned in your earlier question, what's going on in the View `VIEWPURORDERPURCHASEQTY`?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.