Re: Traverse and Retain Values from a Tree

[Vishner@AOL.COM]

Hi Myron,

You might be confusing the SQR parser because you
haven't supplied column variable aliases for the
"PRIOR" columns.
Try coding:

begin-SELECT
LEVEL
BC.COMPONENT_ID
BC.INV_ITEM_ID
BC.QTY_PER
PRIOR BC.COMPONENT_ID           &Prior_Component_ID
PRIOR BC.INV_ITEM_ID            &Prior_Inv_Item_ID
FROM  PS_EN_BOM_COMPS BC
START WITH BC.INV_ITEM_ID = '5500041-B'
CONNECT BY PRIOR BC.COMPONENT_ID = BC.INV_ITEM_ID
end-select

HTH,
Hugh

In a message dated Wed, 13 Sep 2000 11:49:13 AM Eastern Daylight Time, Myron 
Dietz <myron.dietz@OMD.HITACHI.COM> writes:

<< I am writing an SQR that will traverse a database tree, an Oracle self
joined table. I am trying to use the following SQL to get the quantity of a
current item and the id of the item above it so I can calculate the actual
number required in a bill of materials.

begin-SELECT
LEVEL,
BC.COMPONENT_ID,
BC.INV_ITEM_ID,
BC.QTY_PER,
PRIOR BC.COMPONENT_ID,
PRIOR BC.INV_ITEM_ID
FROM  PS_EN_BOM_COMPS BC
START WITH BC.INV_ITEM_ID = '5500041-B'
CONNECT BY PRIOR BC.COMPONENT_ID = BC.INV_ITEM_ID
end-select

This SQL statement works. Unfortunately,  when I put it into the SQR I get
the following error message:

(SQR 5528) ORACLE OPARSE error -1788 in cursor 9:
   ORA-01788: CONNECT BY clause required in this query block
SQL:  select LEVEL, BC.COMPONENT_ID, BC.INV_ITEM_ID, BC.DATE_IN_EFFECT,
      BC.DATE_OBSOLETE, BC.QTY_PER, BC.BOM_STATE, BC.POS_NBR, PRIOR
      BC.COMPONENT_ID, PRIOR BC.INV_ITEM_ID  FROM  PS_EN_BOM_COMPS BC
Error at:


Is the Oracle Prior command supported in SQR  version 4.3.4? Is there
another way, with reasonable run time, that will get the information I
require?

Thank you for your assistance,

Myron Dietz
 >>