Oracle 9i Subselect and Group by

Experts,

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

SELECT 
        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,
        (SELECT MIN(EDI_DATA_ID_VALUE) 
            FROM E_ORD_D10
            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

Open in new window


Any help would be appreciated
LVL 1
JDCamAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Maybe something like this?

SELECT 
        ordnum,
        comp,
        SUM(qty) AS qty,
        SUM(wght) AS wght,
        lev1,
        SKU,
        desc1,
        desc2,
        UOM,
        UPC,
	CB_REF
from (
	SELECT 
        	d5.ord_num AS ordnum,
        	d5.comp_code AS comp,
        	d5.ord_ship_qty AS qty,
        	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,
        	(SELECT MIN(EDI_DATA_ID_VALUE) 
            	FROM E_ORD_D10
            	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 
        ordnum,
        comp,
        lev1,
        SKU,
        desc1,
        desc2,
        UOM,
        UPC,
	CB_REF
/

Open in new window

0
 
JDCamAuthor Commented:
Works Perfect ... Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.