OQL - Nested select statements - with checking on diveded by zero
0
I've added a part of correct working OQL. This piece of code will result in a report containing of only 2 columns. The second one (Fabric) is a calculation with a divided operation. This code is very large because i wanted to prevent the error "divided by zero". Result is below. Are there other solutions for this? FROM "Order".OrderLine AS OL INNER JOIN OL/"Order".OrderLine_Order/"Order"."Order" as O WHERE O/"Order".Order_Supplier = $objSupplier AND O/"Order".Order_ProductGroup=$objProductGroup AND (OL/ProductionStatus='P' OR OL/ProductionStatus='S') GROUP BY OL.SupplyChainMonth SELECT OL.SupplyChainMonth as MonthName, ( CASE WHEN( (FROM "Order".ProductionOrderLine AS OPL INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine as OLS INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine/"Order".OrderLine_Order/"Order"."Order" as OS WHERE OLS.ProductionStatus='P' AND OLS.ProductionPortalStatus='Production' AND OLS.SupplyChainMonth=OL.SupplyChainMonth AND OPL.ProductionType='Bulk_fabric' AND OS/"Order".Order_Supplier=$objSupplier AND OS/"Order".Order_ProductGroup=$objProductGroup SELECT SUM(OPL.PiecesMax)) != NULL and ( FROM "Order".ProductionOrderLine AS OPL INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine as OLS INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine/"Order".OrderLine_Order/"Order"."Order" as OS WHERE OLS.ProductionStatus='P' AND OLS.ProductionPortalStatus='Production' AND OLS.SupplyChainMonth=OL.SupplyChainMonth AND OPL.ProductionType='Bulk_fabric' AND OS/"Order".Order_Supplier=$objSupplier AND OS/"Order".Order_ProductGroup=$objProductGroup SELECT SUM(OPL.PiecesMax)) != 0 ) THEN ROUND((FROM "Order".ProductionOrderLine AS OPL INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine as OLS INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine/"Order".OrderLine_Order/"Order"."Order" as OS WHERE OLS.ProductionStatus='P' AND OLS.ProductionPortalStatus='Production' AND OLS.SupplyChainMonth=OL.SupplyChainMonth AND OPL.ProductionType='Bulk_fabric' AND OS/"Order".Order_Supplier=$objSupplier AND OS/"Order".Order_ProductGroup=$objProductGroup SELECT SUM(OPL.Pieces)) : (FROM "Order".ProductionOrderLine AS OPL INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine as OLS INNER JOIN OPL/"Order".ProductionOrderLine_OrderLine/"Order".OrderLine/"Order".OrderLine_Order/"Order"."Order" as OS WHERE OLS.ProductionStatus='P' AND OLS.ProductionPortalStatus='Production' AND OLS.SupplyChainMonth=OL.SupplyChainMonth AND OPL.ProductionType='Bulk_fabric' AND OS/"Order".Order_Supplier=$objSupplier AND OS/"Order".Order_ProductGroup=$objProductGroup SELECT SUM(OPL.PiecesMax)) * 100 ,0) + '%' ELSE NULL END ) AS FABRIC ORDER BY MonthName