Is this VFP 8 or 9?
If so, unless SET ENGINEBEHAVIOR is 7, the GROUP BY statement still
needs all of the non-calculated fields in the query (or subquery, in
this case) even with your NULL test.
So:
...(SELECT BILL_SEQUENCE_ID, DISABLE_DATE, COUNT(COMPONENT_SEQUENCE_ID)
AS BOMLINECOUNT FROM APPS.BOM_COMPONENTS_B GROUP BY
BILL_SEQUENCE_ID,DISABLE_DATE)...
On May 29, 2009, at 7:20 PM, Vince Teachout wrote:
> 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')
>
[excessive quoting removed by server]
_______________________________________________
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/735c1398-c858-4b97-9dbe-29fcc838a...@information-architecture.com
** 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.
