Re: Question on SELECT LEVEL...CONNECT BY, tree-walking

The CONNECT BY syntax requires you to use the key word PRIOR to identify the parent side of the relationship.
for example:
CONNECT BY PRIOR psf_part = psf_assy
  or
CONNECT BY psf_part = PRIOR psf_assy

hlh_nospam_at_excite.com wrote:

> I'm having some difficulty with SELECT LEVEL...CONNECT BY, as illustrated
> below. Note that in the first query, assembly# 502-23125-00 occurs at level
> 1, with no expansion. However, as shown in the second query, it contains
> several parts. I thought perhaps the level was not being expanded because
> there wasn't another level below, but as shown in listing 3, this is not the
> problem.
>
> I'm hoping that my error is obvious, and that somebody, after he/she recovers
> from the gales of laughter, is kind enough to point it out...
>
> listing 1
> SQL> SELECT level,
> 2 psf_find_nbr,
> 3 psf_part,
> 4 psf_assy
> 5 FROM sec_psf_effective_view
> 6 CONNECT BY psf_part = psf_assy
> 7 START WITH psf_assy = '502-25700-00';
> [snip]
> 1 0016 32.1516.589-19 502-25700-00
> [snip]
> 1 0008 502-25724-00 502-25700-00
> 1 0009 502-23125-00 502-25700-00
> 1 0010 502-25797-00 502-25700-00
> [snip]
>
> listing 2
> SQL> select
> 2 psf_find_nbr,
> 3 from sec_psf_effective_view
> 4 where psf_assy = '502-23125-00'
> 5 order by psf_part
> 0005 200-0636-001
> 0006 200-0647-001
> 0003 260-0216-001
> 0004 260-0217-001
> 0001 269-0023-001
> 0007 320.853660
>
> 6 rows selected.
>
> listing 3
> SQL> select
> 2 psf_find_nbr, psf_part
> 3 from sec_psf_effective_view
> 4 where psf_assy = '32.1516.589-19'
> 5 order by psf_part;
>
> no rows selected
>
> --
> Howard Lee Harkness
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Apr 13 1999 - 22:58:02 CDT