| Hierarchy view [message #655579] |
Sat, 03 September 2016 03:34  |
 |
steveshephard87
Messages: 1
Registered: September 2016
|
Junior Member |
|
|
Hi,
I am trying to create a hierarchy view.
I have a table (TABLE_A) which stores a list of orders. Some of these orders contain sub orders and are stored in another table (TABLE_B). These sub orders can then found again in another table (TABLE_C). This can continue to go down to a number of tables. Therefore you can have a hierarchy of many levels
Is there a way I can calculate how many levels a product goes down to and what the top and bottom orders number are?
Any help would be really appreciated
Many thanks
Steve
|
|
|
|
|
|
| Re: Hierarchy view [message #655586 is a reply to message #655579] |
Sat, 03 September 2016 17:52  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a basic example, given the limited information that you have provided. You should be able to adapt it to your situation. I should point out, however, that this appears to be a bad table design. The usual method is to store such information in one table. As long as you have order id's and sub order id's, you can retrieve the data in a hierarchical fashion without a limit on the number of levels. With the method that you are using, you will need one table per level.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM table_a
2 /
ORDER_ID SUB_ORDER_ID
---------- ------------
1
2 20
3 30
3 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM table_b
2 /
ORDER_ID SUB_ORDER_ID
---------- ------------
20 200
30 300
2 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM table_c
2 /
ORDER_ID SUB_ORDER_ID
---------- ------------
300 3000
1 row selected.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE VIEW hierarchy_view AS
2 SELECT CASE WHEN b.order_id IS NULL THEN 1
3 WHEN c.order_id IS NULL THEN 2
4 ELSE 3
5 END levels,
6 a.order_id top_order_id_a, b.order_id order_id_b, c.order_id order_id_c,
7 COALESCE (c.order_id, b.order_id, a.order_id) bottom_order_id
8 FROM table_a a, table_b b, table_c c
9 WHERE a.sub_order_id = b.order_id(+)
10 AND b.sub_order_id = c.order_id(+)
11 ORDER BY a.order_id, b.order_id, c.order_id
12 /
View created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM hierarchy_view
2 /
LEVELS TOP_ORDER_ID_A ORDER_ID_B ORDER_ID_C BOTTOM_ORDER_ID
---------- -------------- ---------- ---------- ---------------
1 1 1
2 2 20 20
3 3 30 300 300
3 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
