NEWBIE - Selecting MAX(Value) with connect by Prior

From: VB <vodkablokey_at_gmail.com>
Date: 8 Sep 2006 01:50:45 -0700
Message-ID: <1157705445.094115.316840@h48g2000cwc.googlegroups.com>

I am running the following query to pull a Bill of Material (Product build structure) from our ERP suite (Oracle 8i)

select PART_NO,PART_DESCRIPTION,
LEVEL,ENG_CHG_LEVEL,ALTERNATIVE_NO,LINE_ITEM_NO,COMPONENT_PART,COMPONENT_DESCRIPTION,QTY_PER_ASSEMBLY from zz_bom_view t
connect by prior component_part = part_no start with part_no = 'RB9044-00043'

What I am looking for though is to flatten the results so it only returns the maximum revision (ENG_CHG_LEVEL) for the tree, ie the most up to date structure without the previous variations.

I had tried MAX(ENG_CHG_LEVEL) and GROUP BY ENG_CHG_LEVEL but this threw up a "Not a Group by function" error.

Could anyone please advise? Received on Fri Sep 08 2006 - 03:50:45 CDT