I have the following code
Select COALESCE(sh.reference, cast(sh.sales_document_num as char(20))) as CustomerOrderNumber, CAST('ORDL' as char(4)) as Type, CAST('1' as char(1)) as one, '' as two, si.sales_item_num as LineNumber, si.product_code as ProductCode, CASE WHEN si.status = 2 THEN '200' WHEN si.status = 3 THEN '220' WHEN sr.status = 2 THEN '30' ELSE '50' END as Status, si.sales_rqd_quantity as Quantity, si.sales_rqd_quantity - pp.quantity as InOrderQuantity, CAST('0' as char(1)) as seven, pp.quantity as DeliveredInTotal, CAST('0' as char(1)) as NotAvailableQuantity, '''' as nine, ph.despatch_type as DespatchType, ph.package_id as TrackingNumber FROM sales_header sh LEFT JOIN sales_item si ON sh.sales_document_num = si.sales_document_num LEFT JOIN sales_requirement sr ON sh.sales_document_num = sr.sales_document_num and si.sales_item_num = sr.sales_item_num LEFT JOIN package_product pp ON si.sales_document_num = pp.sales_document_num and si.sales_item_num = pp.sales_item_num LEFT JOIN package_header ph ON pp.despatch_num = ph.despatch_num WHERE SH.sales_document_num = '21199927' GROUP BY sh.sales_document_num, si.sales_item_num, si.product_code, si.status, ph.despatch_type, ph.package_id
However I am unsure what to put in the group by clause as I am receiving this error.
ORA-00979: not a GROUP BY expression 00979. 00000 – “not a GROUP BY expression” *Cause:
In the group by , do you have to put the calculation and casts etc.
Any help appreciated
Since it doesn’t look like you are using any aggregate functions in your query and you are just trying to remove duplicate records, I would remove the group by and just do
SELECT DISTINCT... to keep the query a bit simpler.