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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDCamAuthor Commented:
Works Perfect ... Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.