pulling data , and the hiearcy

[ss7415]

i have never come across this , i would appriciate some assistance.

I am using crystal xi and oracel 9i

i have two main fields wo_nbr and a lot_nbr (i am only looking at lot_nbr with a 'W' as the first character).
wo_nbr will be a parameter.

when i return my records with that wo_nbr, i want it to look at the lot_nbr and grab the data associated to those lot_nbr and those accosicated with those , until it captures all the data.

so the data will start at a level and i want to get all the data associated below that level.

if i need to explain more plese let me know.

if i take the W off the lot_nbr, it becomes a Wo_nbr.

 

[PHV]

Something like this ?
WHERE lot_nbr = 'W' || Wo_nbr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ss7415]

well i need to do some kind of loop or something, becuase once i get all the lot_num from the wo_nbr. i need to use take off the 'W' from the lot_nbr (becomes a wo_nbr) and see all the ones below that and so on

 

[ss7415]

This might be better
from this result i take the 'W' off the lot_nbr( it becomes a wo_nbr) then i need to see all the records for that lot_num (without 'W' so its a wo_nbr now) and so on



select wo_nbr,lot_nbr
from wod_table
where wo_nbr = '429611'
and type = '01' and lot_nbr like'W%'

WO_NBR LOT_NBR

429611 W442608
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W487441
429611 W479598
429611 W472213
429611 W479598
429611 W498168

 

[ss7415]

this might be clearer, since i took off the W , i can use it as a wo_nbr and get the data for that, and then get the data for those wo_nbr, and so on

select wo_nbr,lot_nbr ,SUBSTR(lot_nbr,2) wo_nbr
from wod_table
where wo_nbr = '429611'
and type = '01' and lot_nbr like'W%'


WO_NBR LOT_NBR WO_NBR_1

429611 W442608 442608
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W487441 487441
429611 W479598 479598
429611 W472213 472213
429611 W479598 479598
429611 W498168 498168

 

[ss7415]

so basicall my next step is to take wo_nbr1 and get the lot_nbr assocaited to those . then i would repeat the process until it gets to the lowest level.


select wo_nbr,lot_nbr ,SUBSTR(lot_nbr,2) wo_nbr1,qty, max (date_add)
from wod_table
where wo_nbr = '429611'
and type = '01' and lot_nbr like'W%' and qty > 0
group by wo_nbr,lot_nbr ,qty


WO_NBR LOT_NBR WO_NBR1 QTY MAX(DATE_ADD)

429611 W442608 442608 1 9/29/2006
429611 W472213 472213 1 9/27/2006
429611 W479598 479598 1 9/27/2006
429611 W487441 487441 1 9/28/2006
429611 W498168 498168 1 9/30/2006

 

[PHV]

You have to know what a self join is, but be aware that recursion is not an easy thing in SQL.
Do you know what is the deepest level ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ss7415]

is it where i link the same table but name the differently?

not sure how deep they can go.

 

[HelsByTheDock]

Have you tried using the Oracle Hierarchical Query syntax? It gave me quite a few headaches, but its wonderful when you get it working!

I don't have the Oracle docs to hand, but there is a chapter on this in one of the manuals. There is also a pretty good example and explanation at

http://www.databasejournal.com/features/oracle/article.php/3552521