It's been a long day, and I've tracked down an "invalid grouping error"
(or words to this affect) to the following legacy query, where the
problem area is uppercased:
select isbn.segment1 AS ISBN, bic.bill_sequence_id, bic.BOMLineCount AS
linecount from apps.bom_structures_b bom, apps.mtl_system_items_b isbn,
(SELECT BILL_SEQUENCE_ID, DISABLE_DATE, COUNT(COMPONENT_SEQUENCE_ID)
AS BOMLINECOUNT FROM APPS.BOM_COMPONENTS_B GROUP BY BILL_SEQUENCE_ID) bic
where bic.bill_sequence_id = bom.bill_sequence_id
and bom.assembly_item_id = isbn.inventory_item_id
and isbn.organization_id = 70
AND BIC.DISABLE_DATE IS NULL
and substr(isbn.segment1,1,4) = '0390'
and isbn.segment1 in ('list1','list2','etc')
The grouping error occurs in the third line, where disable_date is not
part of the grouping expression. Disable date needs to be checked for
NULL (Third AND clause from the bottom)
My fix for it is below, and I believe it is correct, but as this is a
one shot query, and a lot of billing for a major client depends on it,
and I'm tired, I'd appreciate a second opinion.
Basically, I moved the AND clause up into the Select as a WHERE.
Does this look correct? Thanks!
select isbn.segment1 AS ISBN, bic.bill_sequence_id, bic.BOMLineCount AS
linecount from apps.bom_structures_b bom, apps.mtl_system_items_b isbn,
(SELECT BILL_SEQUENCE_ID, DISABLE_DATE, COUNT(COMPONENT_SEQUENCE_ID)
AS BOMLINECOUNT FROM APPS.BOM_COMPONENTS_B GROUP BY BILL_SEQUENCE_ID
WHERE BIC.DISABLE_DATE IS NULL) bic
where bic.bill_sequence_id = bom.bill_sequence_id
and bom.assembly_item_id = isbn.inventory_item_id
and isbn.organization_id = 70
and substr(isbn.segment1,1,4) = '0390'
and isbn.segment1 in ('list1','list2','etc')
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
