Below is a query where I am trying to add a sub-query.
The main query contains a group by. I am having great difficulty either including or excluding the sub query from the group by.
SQL designer tries to add the entire subselect to the group by, then gives a 'not a group by error' if run
I have been trying to add an aggregate (Min/Max) in hopes it could be excluded from the group by.
I am using a subquery because i will need to add 3 similar queries retrieving values from the the same table
d5.ord_num AS ordnum,
d5.comp_code AS comp,
SUM(d5.ord_ship_qty) AS qty,
SUM(d5.ord_tot_wgt) AS wght,
d5.ord_lev1 AS lev1,
d5.SKU_CODE as SKU,
I.item_Des1 as desc1,
I.item_Des2 as desc2,
d5.wgt_meas_code as UOM,
I.ITEM_UPC as UPC,
d5.ord_unit_wgt as Unit_Wgt,
WHERE COMP_CODE = D5.COMP_CODE
AND ORD_NUM = D5.ORD_NUM
AND ORD_LINE_NUM = D5.ORD_LINE_NUM
AND EDI_DATA_ID_CODE = '2040.N9.02CB') AS CB_REF
FROM e_ord_d5 d5
LEFT JOIN m_item_h I ON I.cust_code = d5.cust_code
AND I.item_code = d5.ord_lev1
AND I.comp_Code = d5.comp_code
WHERE d5.ord_num = :p_ord_num AND d5.comp_code = :p_comp_code
AND d5.ord_ship_qty > 0 AND D5.ord_line_tp = 'R'
GROUP BY d5.ord_num, d5.comp_code, d5.ord_lev1, d5.SKU_CODE, I.item_Des1, I.item_Des2, d5.wgt_meas_code, I.ITEM_UPC, d5.ord_unit_wgt
Any help would be appreciated