

Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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)




SSCEnthusiastic
Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 180,
Visits: 7,598


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)




Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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 
UPDATE MDEP Set MDEP.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 ) WHERE MDEP.MDEP IN (Select MDEP From PO_MX)




Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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)




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 6,748,
Visits: 13,890





Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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.)




Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 6,748,
Visits: 13,890


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.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”  Gail Shaw}
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 Exploring Recursive CTEs by Example Dwain Camps


Post #1546225




Grasshopper
Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17,
Visits: 34


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/sql2005/crossapplyexplained
Again, thank you very much for the responses to my post  it is very much appreciated.




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 6,748,
Visits: 13,890




