You Asked
Hi Tom,
Could you please help me to develop a Query. My requirement is given below. I have a following table called PRODUCT and the structure is given below.
CREATE TABLE PRODUCT(ID NUMBER, NAME VARCHAR2(50), PARENT_ID NUMBER, PRODUCT_TYPE VARCHAR2(1));
I have inserted the following Records.
INSERT INTO PRODUCT VALUES(1,'SAVINGS','', 'P');
INSERT INTO PRODUCT VALUES(2,'SUB_PROD','1', 'S');
INSERT INTO PRODUCT VALUES(3,'ATM','2', 'L');
INSERT INTO PRODUCT VALUES(4,'CREDIT','2', 'L');
INSERT INTO PRODUCT VALUES(5,'wallet','2', 'L');
P-->Product
S-->Sub Product
L-->Service
The Hierarchy is Product-->Sub Product-->Service. Produt is in the TOP of the hierarchy as it doesnt have a parent Id.
No I need to generate a report with the following output.
PRODUCT_ID,PRODUCT_NAME,SUB_PRODUCT_ID,SUB_PRODUCT_NAME,SERVICE_ID,SERVICE_NAME
---------------------------------------------------------------------------------
1,SAVINGS,2,SUB_PROD,3,ATM
1,SAVINGS,2,SUB_PROD,4,Credit
1,SAVINGS,2,SUB_PROD,5,Wallet
Can you please help me to write a Query to get the above output.
and Connor said...
We can use some hierarchy functions to assist
SQL> CREATE TABLE PRODUCT(ID NUMBER, NAME VARCHAR2(50), PARENT_ID NUMBER, PRODUCT_TYPE VARCHAR2(1));
Table created.
SQL> INSERT INTO PRODUCT VALUES(1,'SAVINGS','', 'P');
1 row created.
SQL>
SQL> INSERT INTO PRODUCT VALUES(2,'SUB_PROD','1', 'S');
1 row created.
SQL>
SQL> INSERT INTO PRODUCT VALUES(3,'ATM','2', 'L');
1 row created.
SQL>
SQL> INSERT INTO PRODUCT VALUES(4,'CREDIT','2', 'L');
1 row created.
SQL>
SQL> INSERT INTO PRODUCT VALUES(5,'wallet','2', 'L');
1 row created.
SQL>
SQL> select
2 sys_connect_by_path(name,'-') str,
3 connect_by_isleaf leaf
4 from product
5 start with parent_id is null
6 connect by prior id = parent_id;
STR LEAF
-------------------------------------------------- ----------
-SAVINGS 0
-SAVINGS-SUB_PROD 0
-SAVINGS-SUB_PROD-ATM 1
-SAVINGS-SUB_PROD-CREDIT 1
-SAVINGS-SUB_PROD-wallet 1
5 rows selected.
SQL>
SQL> select *
2 from
3 (
4 select
5 sys_connect_by_path(name,'-') str,
6 connect_by_isleaf leaf
7 from product
8 start with parent_id is null
9 connect by prior id = parent_id
10 )
11 where leaf = 1;
STR LEAF
-------------------------------------------------- ----------
-SAVINGS-SUB_PROD-ATM 1
-SAVINGS-SUB_PROD-CREDIT 1
-SAVINGS-SUB_PROD-wallet 1
3 rows selected.