Re: Start With/Connect By problem

"CJM" <cjmnews04_at_REMOVEMEyahoo.co.uk> wrote in message news:4qegp0FmrbhaU1_at_individual.net...
> 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
>
>

Maybe

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 = (select pspart_no where  ORDER_NO = 'G2000132' and
  RELEASE_NO = '1' AND
  SEQUENCE_NO = '6')
 CONNECT BY PRIOR ps.COMPONENT_PART = ps.PART_NO  order by level, ps.Part_No; Received on Fri Oct 27 2006 - 09:11:21 CDT