Skip to Content
0
Nov 04, 2020 at 03:26 PM

1). [Microsoft][SQL Server Client 10.0][SQL Server]Divide by zero error encountered. "(SWEI)

27 Views

Hi

We have a query setup to show "Purchase order free stock available and Due dates". Suddenly we have now encountered and error of: 1). [Microsoft][SQL Server Client 10.0][SQL Server]Divide by zero error encountered. "(SWEI)

Please see query:

SELECT DISTINCT T0.[WhsCode] AS "Whse", T4.[ItmsGrpNam] AS "Item Group", T0.[ItemCode] AS "Item Code", T0.[Dscription] AS "Item Description", ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) / T2.[U_ConUnit] AS "Rolls", T8.[U_Delivery] AS "ETA", T9.[PrjName] AS "Detail", T0.[DocEntry] AS "PO" FROM POR1 T0 LEFT JOIN OPRJ T9 ON T0.[Project] = T9.[PrjCode] LEFT JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] LEFT JOIN OPOR T8 ON T0.[DocEntry] = T8.[DocEntry] WHERE T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '000' AND '5800-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 OR T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '6000' AND '9500-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 UNION ALL SELECT DISTINCT T5.[WhsCode] AS "Whse", T4.[ItmsGrpNam] AS "Item Group", T5.[ItemCode] AS "Item Code", T5.[Dscription] AS "Item Description", ((SELECT SUM( T6.[OpenQty]) FROM PCH1 T6 WHERE T5.[DocEntry] = T6.[DocEntry] AND T5.[ItemCode] = T6.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T5.[Project] = T1.[Project] AND T5.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) / T2.[U_ConUnit] AS "Rolls", T11.[U_Delivery] AS "ETA", T9.[PrjName] AS "Detail", T5.[DocEntry] AS "PO" FROM PCH1 T5 LEFT JOIN OPRJ T9 ON T5.[Project] = T9.[PrjCode] LEFT JOIN OITM T2 ON T5.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] INNER JOIN OPCH T7 ON T5.[DocEntry] = T7.[DocEntry] LEFT JOIN OPOR T11 ON T5.[BaseRef] = T11.[DocNum] WHERE T7.[isIns] = 'Y' AND T5.[InvntSttus] = 'O' AND T5.[ItemCode] BETWEEN '000' AND '5800-000' AND ((SELECT SUM( T6.[OpenQty]) FROM PCH1 T6 WHERE T5.[DocEntry] = T6.[DocEntry] AND T5.[ItemCode] = T6.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T5.[Project] = T1.[Project] AND T5.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 OR T7.[isIns] = 'Y' AND T5.[InvntSttus] = 'O' AND T5.[ItemCode] BETWEEN '6000' AND '9500-000' AND ((SELECT SUM( T6.[OpenQty]) FROM PCH1 T6 WHERE T5.[DocEntry] = T6.[DocEntry] AND T5.[ItemCode] = T6.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T5.[Project] = T1.[Project] AND T5.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 UNION ALL SELECT DISTINCT T0.[WhsCode], T4.[ItmsGrpNam] AS "Item Group", '', '', Null, Null, '', '' FROM POR1 T0 LEFT JOIN OPRJ T9 ON T0.[Project] = T9.[PrjCode] LEFT JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] WHERE T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '000' AND '5800-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 OR T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '6000' AND '9500-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 UNION ALL SELECT DISTINCT T0.[WhsCode], '', '', T5.[WhsName], Null, Null, '', '' FROM POR1 T0 LEFT JOIN OPRJ T9 ON T0.[Project] = T9.[PrjCode] LEFT JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] INNER JOIN OWHS T5 ON T0.[WhsCode] = T5.[WhsCode] WHERE T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '000' AND '5800-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 OR T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '6000' AND '9500-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 UNION ALL SELECT DISTINCT T0.[WhsCode], 'Z', '', '', Null, Null, '', '' FROM POR1 T0 LEFT JOIN OPRJ T9 ON T0.[Project] = T9.[PrjCode] LEFT JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] INNER JOIN OWHS T5 ON T0.[WhsCode] = T5.[WhsCode] WHERE T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '000' AND '5800-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 OR T0.[LineStatus] = 'O' AND T0.[ItemCode] BETWEEN '6000' AND '9500-000' AND ((SELECT SUM( T3.[OpenQty]) FROM POR1 T3 WHERE T0.[DocEntry] = T3.[DocEntry] AND T0.[ItemCode] = T3.[ItemCode] ) - ISNULL ( (SELECT SUM( T1.[OpenQty]) FROM RDR1 T1 WHERE T0.[Project] = T1.[Project] AND T0.[ItemCode] = T1.[ItemCode] AND T1.[LineStatus] = 'O') , 0 )) <> 0 ORDER BY "WhsCode", "Item Group", "Item Code", "ETA"

Could someone help me understand why we are now getting an error?