February 24, 2014 at 3:50 pm
Hello,
In brief, the query below is generating the error given in the post subject line. I have read dozens of posts on this particular error and they typically seem to resolve to having a constant of some sort in the GROUP BY expression, which is not the case with my GROUP BY clause.
In short, what I'm trying to do with this query is as follow:
I have purchase order tables comprised of the usual header (PO_MX) and detail (PO_DX) tables, linked by PONumber. The columns of interest in these tables are, in the header: a PaymentPosted field, an MDEP code field, and a PrimarySplit percentage field. In the detail section we have a QuanityOrdered field and a Price field.
For all purchase orders having the same MDEP code, the following calculation has to be made: Calculate the total amount of the purchase order as the sum of all (QuantityOrdered * Price). Then, subtract the PaymentPosted amount from the header section. This yields the unpaid portion of the purchase order. Then multiply this value by the percentage (expressed as an integer) housed in the MDEP column from the header.
Once this sum has been calculated for all purchase orders having the same MDEP code, this value then needs to be written to the MDEP table. The MDEP table has, in each row, an MDEP column and a POobligations column.
So, for each set of purchase orders with the same MDEP code, the above calculation has to be made and the resultant figure posted to the proper row of the MDEP table.
If I remove the outermost part of the query, the part that pertains just to updating the MDEP table, and modify the line, "where PO_MX.MDEP = MDEP.MDEP" to read "where PO_MX.MDEP = 'somespecific MDEP code', the query works perfectly. That is, it correctly calculates the correct sum to post to the MDEP table for all purchase orders with the given MDEP code.
As soon as I encapsulate this query within the first two and last two lines of code, I get the error described pointing to the "group by PO_DX.PONumber" line of code.
I would greatly appreciate any suggestions regarding this error.
Thank You
--------------------------------------------------------------------------------------------------------------
Update MDEP
Set MDEP.POObligations =
(
select SUM(PerPO.SumOfPurchaseOrder) from
(
Select PO_MX.PONumber, PO_MX.MDEP, ((PO_MX.PaymentPosted * -1 ) + POD.SumOfLineItems) * (cast(PO_MX.PrimarySplit as decimal(5,2))/100) As SumOfPurchaseOrder
from PO_MX
inner join
(
select PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) As SumOfLineItems
from PO_DX
where (QuantityOrdered * Price) is not NULL
group by PO_DX.PONumber
)
as POD
on PO_MX.PONumber = POD.PONumber
where PO_MX.MDEP = MDEP.MDEP
)
as PerPO
)
where MDEP.MDEP In
(select MDEP from PO_MX)
February 25, 2014 at 10:44 am
It may be that you haven't defined MDEP within the select - it's having to call all the way up to the UPDATE statement to find it, and that's outside of the GROUP range. What happens if you add MDEP into the the query, like this:
Update MDEP
Set MDEP.POObligations =
(
select SUM(PerPO.SumOfPurchaseOrder) from
(
Select PO_MX.PONumber, PO_MX.MDEP, ((PO_MX.PaymentPosted * -1 ) + POD.SumOfLineItems) * (cast(PO_MX.PrimarySplit as decimal(5,2))/100) As SumOfPurchaseOrder
from PO_MX
inner join
(
select PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) As SumOfLineItems
from PO_DX
join MDEP ON PO_MX.MDEP = MDEP.MDEP --add MDEP as a join here
where (QuantityOrdered * Price) is not NULL
group by PO_DX.PONumber
)
as POD
on PO_MX.PONumber = POD.PONumber
--where PO_MX.MDEP = MDEP.MDEP --move this to the ON clause above
)
as PerPO
)
where MDEP.MDEP In
(select MDEP from PO_MX)
February 25, 2014 at 5:40 pm
Doug,
Thank you for your reply. The SQL compiler was not happy with the join clause movement that you had proposed. Nonetheless, your suggestion got me to thinking about a new approach to this query. The result is shown below. The result is quite a bit more compact and, to my eyes at least, a good bit more readable than my first version. The query below runs and it generates the correct results when tested on a modest size data set. Using "Sum(Distinct...)" on columns I'm not actually summing to avoid "Group By" protestations seems a bit tacky, but it works, and as far as I can see, should never cause any problems. Anyhow, apart from adding a little further conditioning to the inner where clause, I believe this will do it for me.
***Actually, check that. Just before hitting post I took one last look at the query and believe I have an error both in it and my testing methodology. I'll have to take a closer look tomorrow...
Thanks again -
UPDATEMDEP
SetMDEP.POObligations =
(
Select (SUM(PO_DX.QuantityOrdered * PO_DX.Price)- SUM(Distinct PO_MX.PaymentPosted)) * SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))
from PO_MX
inner join PO_DX on PO_DX.PONumber = PO_MX.PONumber
Where PO_MX.MDEP = MDEP.MDEP
)
WHEREMDEP.MDEP IN
(Select MDEP From PO_MX)
February 26, 2014 at 6:13 pm
After some further experimentation I've gotten close (or so it seems) but not quite there. My latest attempt at this is shown below. The inner query (starting with Select (D.LineItemTotal) and ending with (Where M1.MDEP = MDEP.MDEP) works perfectly when run by itself and the final where clause is assigned a literal value (i.e., Where M1.MDEP = 'abcd'). This inner query will calculate a correct numeric value for each Purchase Order having an MDEP value matching that in the Where clause.
Now, the final step would be to bracket this inner query with an Update MDEP statement similar to the one shown below, that assigns the sum of the inner query results to the row in the MDEP table with the matching MDEP column. Regardless of how I approach this final step, I run into one error or another.
If anyone has any thoughts on how to approach this I would be most appreciative.
Update MDEP
Set MDEP.POObligations = SUM
(
Select (D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent As POTotal from
(
(select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1
inner join
(select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber
inner join
(select PONumber, SUM(QuantityOrdered * Price) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber
)
Where M1.MDEP = MDEP.MDEP
)
Where MDEP IN (Select MDEP From PO_MX)
February 27, 2014 at 1:59 am
UPDATE m Set
POObligations = x.POObligations
FROM MDEP m
CROSS APPLY (
SELECT
POObligations = (SUM(PO_DX.QuantityOrdered * PO_DX.Price) - SUM(Distinct PO_MX.PaymentPosted))
* SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))
FROM PO_MX
INNER JOIN PO_DX
ON PO_DX.PONumber = PO_MX.PONumber
WHERE PO_MX.MDEP = m.MDEP
) p
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 27, 2014 at 12:21 pm
Chris,
Thank you very much for your posted query. Unfortunately it overlooks one condition that I may not have made as clear as I should have. In the overall calculation that has to be made for each purchase order it cannot be assumed that the PrimarySplit percentage is the same for all Purchase Orders having the same MDEP code. That is, for all those purchase orders with MDEP code 'ABCD', for example, there might be some with a PrimarySplit value of 100%, some with 75%, others with 50%.
In my test data set, in those cases where all purchase orders with a given MDEP code happened to have the same percentage, the query worked perfectly. For those purchase orders of a different MDEP that involved varying percentages, the query failed.
If you have further suggestions of how to handle this last obstacle in the query I would be glad to give it a try.
(By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)
February 27, 2014 at 4:17 pm
Chris,
After some further experimentation I appear to have a working solution as shown below. Basically I adopted an inner query that I had tried from a previous attempt to the outer query of your suggestion. In the testing I've done so far it has calculated all the sums correctly. I'll do a bit of further testing tomorrow, but this seems to be a working solution.
Thanks again,
UPDATE m Set
POObligations = p.POTotal
FROM MDEP m
CROSS APPLY (
Select sum((D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent) As POTotal from
(
(select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1
inner join
(select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber
inner join
(select PONumber, SUM(isnull(QuantityOrdered,0) * isnull(Price,0)) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber
)
Where M1.MDEP = m.MDEP
) p
February 28, 2014 at 1:57 am
Dale Magnant (2/27/2014)
...(By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)
Yep, sorry, my mistake.
Dale Magnant (2/27/2014)
Chris,After some further experimentation I appear to have a working solution as shown below. Basically I adopted an inner query that I had tried from a previous attempt to the outer query of your suggestion. In the testing I've done so far it has calculated all the sums correctly. I'll do a bit of further testing tomorrow, but this seems to be a working solution.
Thanks again,
UPDATE m Set
POObligations = p.POTotal
FROM MDEP m
CROSS APPLY (
Select sum((D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent) As POTotal from
(
(select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1
inner join
(select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber
inner join
(select PONumber, SUM(isnull(QuantityOrdered,0) * isnull(Price,0)) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber
)
Where M1.MDEP = m.MDEP
) p
I think you can simplify this to the following:
UPDATE m SET
POObligations = p.POTotal
FROM MDEP m
CROSS APPLY (
SELECT
POTotal = SUM((dx.LineItemTotal - mx.PaidToDate) * CAST(mx.PrimarySplit AS DECIMAL(5,2))/100)
FROM PO_MX mx
INNER JOIN (
SELECT
PONumber,
LineItemTotal = ISNULL(SUM(QuantityOrdered * Price),0)
FROM PO_DX
GROUP BY PONumber
) dx
ON mx.PONumber = dx.PONumber
WHERE mx.MDEP = m.MDEP
) p
- because neither of the two subselects of PO_MX perform any aggregation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 28, 2014 at 8:33 am
Chris,
I tested your latest post and yes, it does indeed calculate all sums correctly using the same test data set. There was one minor typo I wanted to mention for others who might be following along: the expression "dx.LineItemTotal - mx.PaidToDate" needed changing to "dx.LineItemTotal - mx.PaymentPosted".
Your original post introduced me to the 'Cross Apply' expression which I had never previously used. Rummaging around on the web I found the following article to be helpful in understanding it a bit further;
http://sqlserverplanet.com/sql-2005/cross-apply-explained
Again, thank you very much for the responses to my post - it is very much appreciated.
February 28, 2014 at 8:43 am
Any time Dale.
Check out the two APPLY articles by Paul White in my signature too, they are an excellent walkthrough with examples.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply