Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: connect by clause
shankar72 <member33008_at_dbforums.com> wrote in message news:<3097372.1057870615_at_dbforums.com>...
> i have written this query on test database works fine but when we
> transfered both tables and the query to production we are getting two
> different results
>
> the test version of oracle is- Oracle8i Enterprise Edition
> Release 8.1.7.0.0
> the procuction version is -Oracle8i Enterprise Edition Release 8.1.7.4.0
> is this causing the problem?
>
> SELECT 1 nivel, p.part_no item_number, ' ' mli, p.description, '1'
> quantity, 'EA' uom,'9' make
> FROM table_name p
> WHERE part_no = '*'
> UNION ALL
> SELECT a.nivel, a.part item_number, ml_no mli, b.description,
> a.quantity, b.uom,
> b.make
> FROM (SELECT LEVEL + 1 nivel, a.*
> FROM essen_bom a
> CONNECT BY assembly = PRIOR part AND assembly <> part
> START WITH assembly = '*'
> ORDER BY ess_hier_order) a,
> table_name b
> WHERE a.part = b.part_no
Hard to tell, not enough information. BTW, it's not a good idea to have ORDER BY in CONNECT BY subquery. Without it, result is ordered in order of hierarchy. With it, you actually lose your hierarchy order. Received on Fri Jul 11 2003 - 16:59:56 CDT