CODEWITHSUNDEEP
sqloracle-database
javanoob
javanoob

Reputation: 6410

Oracle Hierarchical query - how to start with selected node

I am trying to write a hierarchical query for the below table but not able to get the data in the format i need..

Here is the data in the table:

+---------+-----------+-------------+
| ITEM_ID | FATHER_ID | DESCRIPTION |
+---------+-----------+-------------+
|    1019 |      1018 | Level 4     |
|    1018 |      1010 | Level 3     |
|    1010 |         2 | Level 2     |
|       2 |         0 | Level 1     |
+---------+-----------+-------------+

here is the query i came up with after lot of googling:

SELECT
SYS_CONNECT_BY_PATH(DESCRIPTION, '\') AS "Path"
FROM
STRUCTURE
START WITH ITEM_ID = 1019
CONNECT BY PRIOR FATHER_ID = ITEM_ID

The output i am getting is :

+---------------------------+
| \Level 4                  |
| \Level 4\Level 3          |
| \Level 4\Level 3\Level 2  |
| \Level 4\Level 3\Level 1  |
| \Level 4\Level 3\Level 1\ |
+---------------------------+

but how to get something like this(just one row):

\Level 1\Level 2\Level 3\Level 4

What am i missing in my query?

Upvotes: 0

Views: 110

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23371

You need to know the root of your levels and as suggested on the comment filter the item_id you want. Also you inverted the connect by statement. The right query for the problem on your question is:

 SELECT SYS_CONNECT_BY_PATH(description, '\') AS Path
   FROM STRUCTURE
  WHERE ITEM_ID = 1019
CONNECT BY PRIOR ITEM_ID = FATHER_ID
  START WITH FATHER_ID = 0;

But if you do not know the last item of your levels just do as this:

 SELECT SYS_CONNECT_BY_PATH(description, '\') AS Path
   FROM STRUCTURE
  WHERE ITEM_ID = (select max(item_id) from STRUCTURE)
CONNECT BY PRIOR ITEM_ID = FATHER_ID
  START WITH FATHER_ID = 0;

But be aware that this second query is just for a model that is ordered and the last level is the final one.

See it here on SQLFiddle

Upvotes: 3

Related Questions