0

I have sample data that shows a hierarchy between labs (locations).

 CREATE TABLE "DEMO"."LABS" 
   (
    "LABID" VARCHAR2(20 BYTE), 
    "PARENT" VARCHAR2(20 BYTE)
   )

A lab has a single parent and therefore n ancestors. I found a way to figure out the entire list of ancestors given a specific lab ID on Oracle's website. 

SELECT LABID
   FROM LABS
   START WITH LABID = 111
   CONNECT BY PRIOR LABS.PARENT = LABS.LABID;

Now I would like to create a view from it that iterates through all the lab identifiers.

I tried a few things (including a LEFT JOIN) but never quite managed to get the right result.

If my table contains:

 -----------------
| LAB_ID | PARENT |
 -----------------
| 1      | NULL   |
| 11     | 1      |
| 111    | 11     |
 -----------------

I would like a VIEW that gives me:

 -------------------
| LAB_ID | ANCESTOR |
 -------------------
| 1      | NULL     |
| 11     | 1        |
| 111    | 1        |
| 111    | 11       |
 -------------------

Notice how, now, I get 2 entries for lab 111. The whole point of the exercise is I want to be able to have a simple way of querying the data i.e.:

SELECT ANCESTOR FROM my_view WHERE LAB_ID = ?

I noticed this question and answer but I failed to fully understand it.

David Brossard
  • 13,584
  • 6
  • 55
  • 88
  • 1
    Tom Kyte [seems to suggest](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:31263710790993) just flipping the values in the `CONNECT BY`. Not exactly sure if that works in your scenario, however. – Mick Mnemonic Mar 29 '19 at 22:38
  • For what it's worth, here is the PostgreSQL way of doing it: http://www.postgresqltutorial.com/postgresql-recursive-view/ – David Brossard Apr 03 '19 at 19:38

0 Answers0