I am trying to use the following query and it returns the error "Not in aggregate function or group by clause: org.hsqldb.Expression@1364b53 in statement [SELECT "OrdersLine"."ProdLine", "Product"."Product Name", "Colour"."Colour", "Order"."Rate", COALESCE ( "OR"."OrderQty", 0.0 ) AS "Ord", COALESCE ( "PMHTdy"."ProdQtyToday", 0.0 ) AS "PrTdMH", COALESCE ( "PUnTdy"."ProdQtyToday", 0.0 ) AS "PrTdUn", COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "TotProd", COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "BalProd", ( COALESCE ( "PMHTdy"."ProdQtyToday", 0.0 ) ) + ( COALESCE ( "PUnTdy"."ProdQtyToday", 0.0 ) ) AS "ValTod", COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "TotVal" FROM "OrdersLine" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "OrdQty" ) AS "OrderQty" FROM "OrdersLine" WHERE "OrdDate" <= :RpDt GROUP BY "Order No ID" ) AS "OR" ON "OrdersLine"."Order No ID" = "OR"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine" WHERE "ProdDate" = :RpDt GROUP BY "Order No ID" ) AS "PMHTdy" ON "OrdersLine"."Order No ID" = "PMHTdy"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine" WHERE "ProdDate" = :RpDt GROUP BY "Order No ID" ) AS "PUnTdy" ON "OrdersLine"."Order No ID" = "PUnTdy"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQty" FROM "OrdersLine" WHERE "ProdDate" <= :RpDt GROUP BY "Order No ID" ) AS "TotProd" ON "OrdersLine"."Order No ID" = "TotProd"."Order No ID" INNER JOIN "Order" ON "OrdersLine"."Order No ID" = "Order"."Order ID" INNER JOIN "Product" ON "Order"."Product ID" = "Product"."ID Number" INNER JOIN "Colour" ON "Order"."Colour ID" = "Colour"."ID Number" WHERE ( "Order"."CloseDate" IS NULL AND :RpDt >= "OrdersLine"."OrdDate" ) OR ( :RpDt >= "OrdersLine"."OrdDate" AND :RpDt < "Order"."CloseDate" ) GROUP BY "OrdersLine"."ProdLine", "Product"."Product Name", "Colour"."Colour", "OR"."OrderQty", "PMHTdy"."ProdQtyToday", "PUnTdy"."ProdQtyToday", "TotProd"."ProdQty", "Order"."Rate" ORDER BY "Order"."Product ID"]
The query is as follows:
Code: Select all
SELECT "OrdersLine"."ProdLine", "Product"."Product Name", "Colour"."Colour", "Order"."Rate", COALESCE ( "OR"."OrderQty", 0.0 ) AS "Ord", COALESCE ( "PMHTdy"."ProdQtyToday", 0.0 ) AS "PrTdMH", COALESCE ( "PUnTdy"."ProdQtyToday", 0.0 ) AS "PrTdUn", COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "TotProd", COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "BalProd", ( COALESCE ( "PMHTdy"."ProdQtyToday", 0.0 ) ) + ( COALESCE ( "PUnTdy"."ProdQtyToday", 0.0 ) ) AS "ValTod", COALESCE ( "TotProd"."ProdQty", 0.0 ) AS "TotVal" FROM "OrdersLine" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "OrdQty" ) AS "OrderQty" FROM "OrdersLine" WHERE "OrdDate" <= :RpDt GROUP BY "Order No ID" ) AS "OR" ON "OrdersLine"."Order No ID" = "OR"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine" WHERE "ProdDate" = :RpDt GROUP BY "Order No ID" ) AS "PMHTdy" ON "OrdersLine"."Order No ID" = "PMHTdy"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine" WHERE "ProdDate" = :RpDt GROUP BY "Order No ID" ) AS "PUnTdy" ON "OrdersLine"."Order No ID" = "PUnTdy"."Order No ID" LEFT OUTER JOIN ( SELECT "Order No ID", SUM( "ProdQty" ) AS "ProdQty" FROM "OrdersLine" WHERE "ProdDate" <= :RpDt GROUP BY "Order No ID" ) AS "TotProd" ON "OrdersLine"."Order No ID" = "TotProd"."Order No ID" INNER JOIN "Order" ON "OrdersLine"."Order No ID" = "Order"."Order ID" INNER JOIN "Product" ON "Order"."Product ID" = "Product"."ID Number" INNER JOIN "Colour" ON "Order"."Colour ID" = "Colour"."ID Number" WHERE ( "Order"."CloseDate" IS NULL AND :RpDt >= "OrdersLine"."OrdDate" ) OR ( :RpDt >= "OrdersLine"."OrdDate" AND :RpDt < "Order"."CloseDate" ) GROUP BY "OrdersLine"."ProdLine", "Product"."Product Name", "Colour"."Colour", "OR"."OrderQty", "PMHTdy"."ProdQtyToday", "PUnTdy"."ProdQtyToday", "TotProd"."ProdQty", "Order"."Rate" ORDER BY "Order"."Product ID"
Thanks
Amit