ORA-00979 Group by Error (The BL001 is highlighted when I get the error)

Toad for Oracle & DevOps Toolkit
1

I am very new to this and have no idea what is wrong with my sequel. Can someone please help? I run this via Toad to test it before using it in my EDI map.

Thanks in advance.

--get header
select  distinct trunc(bl001.date_updated) inv_date,
    TO_CHAR(sysdate, 'YYYY/MM/DD') CURRENTDATE,
    bl001.bl_num inv_num,
    bl001.ct006_id,
    bl001.invoice_printed,
    bl001.invoice_total,
    bl001.rpt_date ship_date,
    bl001.via,
    nvl(ct010.scac_code, 'KMCN') scac_code,
    nvl(substr( op040.customer_po, 1, (instr( op040.customer_po, ' ') - 1)), op040.customer_po ) customer_po,
    substr(op040.customer_po, 1, 4) po4,
    edi850.po_date cust_po_date,
    ct002.name st_name,
    ct002.street_address_1 st_addr1,
    ct002.street_address_2 st_addr2,
    ct002.city st_city,
    ct002.ct020_state_province_id st_st,
    substr(ct002.zip, 1, 5) st_zip,
    nvl(ct002.duns_number, 'NA') ship_to_duns,
    ct001.name so_name,
    ct001.duns_number from_duns,
    bl001.ct048_term_id,
    ct048.discount_pct,
    ct048.description,
    ar001.due_date,
    bl001.bl_date + ct048.discount_days disc_due,
    decode(bl001.ct002_id, '1', 'CN','DI', 'DO') inv_type,
    ROUND(sum(nvl(BL002.extension,0)) * (CT048.DISCOUNT_PCT)/100,2) discount_amt,
    (select ct001a.name
     from kncdba.customers ct001a,
          kncdba.locations ct006
     where bl001.ct006_id = ct006.id
       and ct006.ct001_id = ct001a.id) se_name
            
from kncdba.terms ct048,
    kncdba.ar_ledgers ar001,
    kncdba.vendors ct010,
    kncdba.customers ct001,
    kncdba.invoice_details bl002,
    kncdba.ship_locations ct002,
    kncdba.order_headers op040,
    kncdba.warehouse_to_be_shipped op043,
    kncdba.bills_of_ladings bl001,
    kncdba.edi850
        
where   bl001.ct001_id in ('157537')           	
    and bl001.invoice_printed = '1'
    and bl001.bl_num = '97022490'
    and bl001.bl_num = op043.bl001_bl_num
    and op043.op041_op040_num = op040.num
    and op040.customer_po = edi850.customer_po
    and bl001.ct002_ct001_id = ct002.ct001_id
    and bl001.ct002_id = ct002.id
    and bl001.ct048_term_id = ct048.term_id
    and bl001.bl_num = ar001.invoice_num
    and ar001.trans_type = 'INV'
    and bl001.bl_num = bl002.bl001_bl_num
    and bl001.ct001_id = ct001.id
    and bl001.ct010_vendor_id = ct010.vendor_id(+)

group by trunc(bl001.date_updated),
    TO_CHAR(sysdate, 'YYYY/MM/DD'),
    bl001.bl_num,
    bl001.ct006_id,
    bl001.invoice_printed,
    bl001.invoice_total,
    bl001.rpt_date,
    bl001.via,
    nvl(ct010.scac_code, 'KMCN'),
    nvl(substr( op040.customer_po, 1, (instr( op040.customer_po, ' ') - 1)), op040.customer_po ),
    substr(op040.customer_po, 1, 4),
    edi850.po_date,
    ct002.name ,
    ct002.street_address_1,
    ct002.street_address_2,
    ct002.city,
    ct002.ct020_state_province_id,
    substr(ct002.zip, 1, 5),
    nvl(ct002.duns_number, 'NA'),
    ct001.name,
    ct001.duns_number,
    bl001.ct048_term_id,
    decode(ar001.discount_amt, null, null, bl001.bl_date + ct048.discount_days),
    ct048.discount_pct,
    ct048.description,
    ar001.due_date,
    bl001.bl_date + ct048.discount_days,
	decode(bl001.ct002_id, '1', 'DI', 'DO'),
    to_char(add_months(bl_date, 1), 'YYYYMMDD')
order by bl001.bl_num
2

My suggestion would be to start small. This is a big SQL for someone who is new to this.

Start with a working SQL, making small changes, running often so you know where the problem is.

The problem in this case is that when you have a SUMmed column, you need to group by everything that is not SUMmed.

If it were me, I'd start with the following.

select  distinct trunc(bl001.date_updated) inv_date,
    ROUND(sum(nvl(BL002.extension,0)) * (CT048.DISCOUNT_PCT)/100,2) discount_amt,

from kncdba.terms ct048,
    kncdba.ar_ledgers ar001,
    kncdba.vendors ct010,
    kncdba.customers ct001,
    kncdba.invoice_details bl002,
    kncdba.ship_locations ct002,
    kncdba.order_headers op040,
    kncdba.warehouse_to_be_shipped op043,
    kncdba.bills_of_ladings bl001,
    kncdba.edi850
      
where   bl001.ct001_id in ('157537')           	
    and bl001.invoice_printed = '1'
    and bl001.bl_num = '97022490'
    and bl001.bl_num = op043.bl001_bl_num
    and op043.op041_op040_num = op040.num
    and op040.customer_po = edi850.customer_po
    and bl001.ct002_ct001_id = ct002.ct001_id
    and bl001.ct002_id = ct002.id
    and bl001.ct048_term_id = ct048.term_id
    and bl001.bl_num = ar001.invoice_num
    and ar001.trans_type = 'INV'
    and bl001.bl_num = bl002.bl001_bl_num
    and bl001.ct001_id = ct001.id
    and bl001.ct010_vendor_id = ct010.vendor_id(+)

group by trunc(bl001.date_updated)

If that works (and returns the number of rows that you want), add the subquery back in. You'll probably need to make changes to the group by to get it working again.

Once that is working, add some more to the SELECT part and adjust your GROUP BY to get it working again. And so on.

3

Thank you for your help!