cancel
Showing results for 
Search instead for 
Did you mean: 

Copy SAP Query into SQL Server Management Studio

RahF
Participant
0 Kudos

Hi Experts

Not sure if I can ask this questions in this forum

I have a query in SAP B1, it works perfectly, I want to use this query and create a view based on this query to do some analysis in Power BI

But the result I am getting in the SQL Server Management Studio is missing the Total, but if I run the query in SAP it works without any problem

Below is the query in SAP and a picture of the View in SQL Server Management Studio, can someone please help me out?

SELECT T0.[DocNum] as'Prodn.Order #', 
T0.[U_CustName], 
T0.[DueDate] as 'Build Date', 
T0.[ItemCode], 
T1.[ItemName] as 'Product Description', 
sum(T0.[PlannedQty]) as 'Qty', 
sum(T0.[CmpltQty]) as 'Completed Qty',
T4.LineTotal as 'Total',
T0.[CloseDate] as 'Completed Date'


FROM OWOR T0 left join  OITM T1 on T0.[ItemCode]=T1.[ItemCode]
INNER JOIN WOR1 T2 ON T0.DocEntry = T2.DocEntry
left join  ORDR T3 on T3.[DocNum] = T0.[OriginNum]
left JOIN RDR1 T4 ON T4.DocEntry = T3.DocEntry AND T4.ItemCode=T0.ItemCode and T4.[U_Prod_Order_No] = T0.[DocNum]


WHERE T2.[ItemCode] like '%Labour%' and T0.[DueDate] between [%0] and [%1] and T0.[Status] IN ('R','L')


GROUP BY T0.[DocNum], T0.[U_CustName], T0.[PostDate], T0.[ItemCode], T1.[ItemName],T0.[DueDate],T4.LineTotal, T0.[CloseDate]

Regards

Rahul

Accepted Solutions (0)

Answers (1)

Answers (1)

msundararaja_perumal
Active Contributor
0 Kudos

Hello,

Add isnull on the total, like isnull(T4.LineTotal) as 'Total' in the query.

Thanks.