July 23, 2015 at 8:35 am
I have a sql script that is used for a lot for reports. I need to SUM Boxcount and the way I do that is a sub query and it works fine as long as they only enter one
UOMPSCHEDULENUMBER but if they enter more than one the I get the sub query returns more than 1 value error. What can I do??
SELECT *
,ISNULL(UPSSortID, UomlPaintSort) AS UPSSortID
,CAST(jmapartlongdescriptiontext AS VARCHAR(50)) AS jmapartlongdescriptiontext22
,(
SELECT sum(uomlboxcount)
FROM SalesOrderLines
,SalesOrders
WHERE SalesOrderLines.omlSalesOrderID = SalesOrders.ompSalesOrderID
AND ({?WHERECLAUSE})
GROUP BY UOMPSCHEDULENUMBER
) AS Boxcount
FROM SalesOrders
INNER JOIN SalesOrderLines ON SalesOrders.ompSalesOrderID = SalesOrderLines.omlSalesOrderID
INNER JOIN SalesOrderDeliveries ON SalesOrderLines.omlSalesOrderID = SalesOrderDeliveries.omdSalesOrderID
AND SalesOrderLines.omlSalesOrderLineID = SalesOrderDeliveries.omdSalesOrderLineID
INNER JOIN SalesOrderJobLinks ON SalesOrderDeliveries.omdSalesOrderID = SalesOrderJobLinks.omjSalesOrderID
AND SalesOrderDeliveries.omdSalesOrderLineID = SalesOrderJobLinks.omjSalesOrderLineID
AND SalesOrderDeliveries.omdSalesOrderDeliveryID = SalesOrderJobLinks.omjSalesOrderDeliveryID
INNER JOIN Jobs ON SalesOrderJobLinks.omjJobID = Jobs.jmpJobID
LEFT JOIN UPAINTSORT ON SalesOrderLines.UOMLPaintSort = UPAINTSORT.UPSSortID
INNER JOIN (
SELECT *
FROM JobAssemblies
WHERE jmaLevel <> 1
AND (
left(jmaPartID, 3) = 'HM_'
OR left(jmaPartID, 3) = 'OK_'
OR left(jmaPartID, 3) = 'CH_'
OR left(jmaPartID, 3) = 'SG_'
)
) AS JobAssemblies ON Jobs.jmpJobID = JobAssemblies.jmaJobID
WHERE uompsched
AND ompClosed = '0'
July 23, 2015 at 8:55 am
I think you need to correlate your subquery to the parent.
SELECT sum(uomlboxcount)
FROM SalesOrderLines sol
,SalesOrders so
WHERE sol.omlSalesOrderID = so.ompSalesOrderID
AND so.ompSalesOrderID = SalesOrders.ompSalesOrderID
AND ({?THE REST OF YOUR WHERE CLAUSE})
GROUP BY UOMPSCHEDULENUMBER
) AS Boxcount
FROM SalesOrders
Notice I aliased the tables in the subquery to differentiate what is joining to what.
Please make sure this is what you want. Without the DDL for the tables, I can't test it.
July 23, 2015 at 9:09 am
What you need to worry about first is what SUM you want when there's more than one value specified. Do you want the SUM across all specified numbers? Or, do you want to get additional records listing each specified number? If the SUM across all specified numbers is desired, then just get rid of the GROUP BY in that subquery. If it's the latter instead, you'll probably want to CROSS APPLY that query and include the field you're using GROUP BY on within the SELECT for that subquery. You'll probably also want to correlate the sub-query, as Ed Wagner indicated, by tying the sales order identifier from the outer query to the WHERE condition for it in the sub-query. This is what that might look like: (note that I "prettified" the formatting and used IN instead of the multiple ORs)
SELECT *
,ISNULL(UPSSortID, UomlPaintSort) AS UPSSortID
,CAST(jmapartlongdescriptiontext AS VARCHAR(50)) AS jmapartlongdescriptiontext22
,BC.UOMPSCHEDULENUMBER
,BC.BoxCount
FROM SalesOrders AS SO
INNER JOIN SalesOrderLines AS SOL
ON SO.ompSalesOrderID = SOL.omlSalesOrderID
INNER JOIN SalesOrderDeliveries AS SOD
ON SOL.omlSalesOrderID = SOD.omdSalesOrderID
AND SOL.omlSalesOrderLineID = SOD.omdSalesOrderLineID
INNER JOIN SalesOrderJobLinks AS SOJL
ON SOD.omdSalesOrderID = SOJL.omjSalesOrderID
AND SOD.omdSalesOrderLineID = SOJL.omjSalesOrderLineID
AND SOD.omdSalesOrderDeliveryID = SOJL.omjSalesOrderDeliveryID
INNER JOIN Jobs AS J
ON SOJL.omjJobID = J.jmpJobID
LEFT JOIN UPAINTSORT AS UPS
ON SOL.UOMLPaintSort = UPS.UPSSortID
INNER JOIN (
SELECT *
FROM JobAssemblies
WHERE jmaLevel <> 1
AND LEFT(jmaPartID, 3) IN ('HM_','OK_', 'CH_', 'SG_')
) AS JA
ON J.jmpJobID = JA.jmaJobID
CROSS APPLY (
SELECT UOMPSCHEDULENUMBER, SUM(uomlboxcount) AS BoxCount
FROM SalesOrderLines AS SOL1
INNER JOIN SalesOrders AS SO1
ON SOL1.omlSalesOrderID = SO1.ompSalesOrderID
AND SO1.ompSalesOrderID = SO.ompSalesOrderID-- This is the tie that makes it CORRELATED
WHERE ({?WHERECLAUSE})
GROUP BY UOMPSCHEDULENUMBER
) AS BC
WHERE uompsched
AND ompClosed = '0'
Let us know what works...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2015 at 2:11 pm
what I get with the original query is a total box count for the UOMPSCHEDULENUMBER for 072115-01 I would get 812 for every line but with these I get the sum of the order line on every line which inflates my box count to 3892.
July 24, 2015 at 6:19 am
kat35601 (7/23/2015)
what I get with the original query is a total box count for the UOMPSCHEDULENUMBER for 072115-01 I would get 812 for every line but with these I get the sum of the order line on every line which inflates my box count to 3892.
This leaves the question of which SUM you want, unanswered. Clearly, the supplied query isn't the one you want, but the question remains as to what SUM is desired when the user specifies multiple values. Also, as we have no sample data to work with, it's rather difficult to know how to write the code to meet your specific needs. We don't have DDL to describe each table involved, nor do we have any indication as to what a record in each such table represents, so we're seriously flying blind here. Please do what you can to fill the gaps....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply