Skip to Content
0
May 29, 2018 at 08:27 PM

Running total sql SAP B1 - Not found a proper way to resolve it

234 Views Last edit May 30, 2018 at 01:41 PM 3 rev

I tried to have a running total row at the bottom of my table by running this following SQL code.

<code>SELECT
T1.[ItemCode],T4.[ItmsGrpNam],CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE
SUM(T1.[OpenQty])ENDAs'Missing Quantity'FROM ORDR T0
INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode
INNERJOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod

WHERE 
 T3.[WhsCode]= T1.[WhsCode]and  T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0UNIONALLSELECT'See','Total Missing Quantity',CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE
SUM(T1.[OpenQty])ENDFROM ORDR T0
INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode
INNERJOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod

WHERE 
 T3.[WhsCode]= T1.[WhsCode]and  T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')

I have this error message ' 1). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] The 'OITW.OnHand' column is not valid in the selection list because it is not contained in an aggregate function or in the clause BY GROUP. 2). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] Can not prepare statements. 'User Defined Values' (CSHS)'

When I try to

<code>GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0

In the second part of the query, I get multiple rows when I only want one that sum all.

I spent the day to figure this out and there's multiple answers on the NET but I don't know what i'm doing wrong.

Does anyone can help me with this issue? Thanks in advance.