Unsure of what to put in the GROUP BY statement

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:
*Action:

In the group by , do you have to put the calculation and casts etc.

Any help appreciated

Answer

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.

Advertisement

Answer