Start With/Connect By problem

I'm trying to help a colleague with an SQL problem; he's trying to list each item in a Bill Of Materials (BOM) - and Start With/ Connect By seems to be ideal for cracking this particular nut.

To start with, we have the following code:

SELECT ps.PART_NO Parent, ps.Contract, LEVEL, ps.COMPONENT_PART Component, ps.QTY_PER_ASSEMBLY LevelQty, ps.PRINT_UNIT UOM FROM IFSAPP.PROD_STRUCTURE ps
WHERE ps.CONTRACT = 'EMGA'
START WITH ps.PART_NO = 'CLR027B'
CONNECT BY PRIOR ps.COMPONENT_PART = ps.PART_NO order by level, ps.Part_No;

This works fine - it produces a list of all the parts at each level of the BOM. The problem is we don't know what the top-level Part No is (eg. CLR027B). We do however know the the order details, from which we can determined the the top-level part no...

So Shop Order 'G2000132-1-1' has a Part No of 'CLR027B' and belongs to Contract 'EMGA'. Using this information, we ought to be able to feed in the shop order information, pull out the top-level Part No and Contract and thus list on the BOM entries for that part.

However, when I link in the SHOP_ORD view, it fouls up the existing code - the query then only shows BOM entries for the top level (CLR027B). The reason is that the link to SHOP_ORD view acts as a constraint that filters by Part No CLR027B:

SELECT ps.PART_NO Parent, ps.Contract, LEVEL, ps.COMPONENT_PART Component, ps.QTY_PER_ASSEMBLY LevelQty, ps.PRINT_UNIT UOM FROM IFSAPP.PROD_STRUCTURE ps
inner join
  (select Part_no, Contract
  from IFSAPP.SHOP_ORD
  where ORDER_NO = 'G2000132' and
  RELEASE_NO = '1' AND
  SEQUENCE_NO = '6') soo on soo.Part_No = ps.Part_No and soo.Contract = ps.Contract
WHERE ps.CONTRACT = 'EMGA'
START WITH ps.PART_NO = 'CLR027B'
CONNECT BY PRIOR ps.COMPONENT_PART = ps.PART_NO order by level, ps.Part_No;

The problem is that I don't know how to re-structure the query to so that this selection criteria only applies to the initial (ie top) layer of the BOM. Can this query be tweaked to provide what we need? Ideally I'd like an all-in-one solution if possible - I'm not sure if we have the capability for a multi-stage query - I think this query is going to be run through Crystal Reports - I'm not sure if we have the capability of running more than one SQL statement in the version of crystal we have (I'll try to confirm this shortly). But if it is a two-stage-only process, the it's better we face the music sooner rather than later.

Thanks in advance

Chris Received on Fri Oct 27 2006 - 08:43:59 CDT