oracle sql hierarchical queries

Question

I have two tables:

CREATE TABLE CATEGORY (
  cat_id      NUMBER PRIMARY KEY,
  cat_ust_id  NUMBER REFERENCES Category( cat_id )
);

CREATE TABLE PRODUCT (
  cat_1       NUMBER REFERENCES Category( cat_id ),
  cat_2       NUMBER REFERENCES Category( cat_id ),
  cat_3       NUMBER REFERENCES Category( cat_id ),
  cat_4       NUMBER REFERENCES Category( cat_id ),
  cat_id      NUMBER PRIMARY KEY
                     REFERENCES Category( cat_id )
);

INSERT INTO Category
SELECT    1, NULL FROM DUAL UNION ALL
SELECT    2, NULL FROM DUAL UNION ALL
SELECT   11,    1 FROM DUAL UNION ALL
SELECT  112,   11 FROM DUAL UNION ALL
SELECT  202,   24 FROM DUAL UNION ALL
SELECT   24,    2 FROM DUAL UNION ALL
SELECT 2035,  203 FROM DUAL UNION ALL
SELECT  203,   20 FROM DUAL UNION ALL
SELECT   20,    2 FROM DUAL;

INSERT INTO Product
SELECT 1, NULL, NULL, NULL, 11 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 202 FROM DUAL UNION ALL
SELECT 1, NULL, NULL, NULL, 112 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 2035 FROM DUAL;

In PRODUCT table, I have to update some column according to CATEGORY table hierarchy to get this result:

cat_1 | cat_2 | cat_3 | cat_4 | cat_id
---------------------------------------
    1 |    11 |  NULL |  NULL |     11
    2 |    24 |   202 |  NULL |    202
    1 |    11 |   112 |  NULL |    112
    2 |    24 |   203 |  2035 |   2035

Should I create a procedure or function for this?

Please provide your tables' structure, some sample data and the result you'd like to get for this sample data. Or better yet, and [sqlfiddle](http://sqlfiddle.com/). — Mureinik, Jan 13 '16 at 17:59
You can see in this link, thank you @Mureinik....http://i.stack.imgur.com/K8Hwx.png — Bazaaran Corbi, Jan 13 '16 at 18:04
Your tables are not well structured. You may reconsider the design of your database. Try to apply the normalization techniques. — yasserkabbout, Jan 13 '16 at 18:11
thnx @yasserkabbout, i'll take into r suggestion but I didnt give full table structure, it is just partly. — Bazaaran Corbi, Jan 13 '16 at 18:14
You welcome. The update should be an automated one for all rows and columns? or you can perform them one by one? — yasserkabbout, Jan 13 '16 at 18:16

score 1 · Accepted Answer · answered Jan 13 '16 at 20:12

Oracle Setup:

CREATE TABLE Category ( CAT_ID, CAT_UST_ID ) AS
SELECT    1, NULL FROM DUAL UNION ALL
SELECT    2, NULL FROM DUAL UNION ALL
SELECT   11,    1 FROM DUAL UNION ALL
SELECT  112,   11 FROM DUAL UNION ALL
SELECT  202,   24 FROM DUAL UNION ALL
SELECT   24,    2 FROM DUAL UNION ALL
SELECT 2035,  203 FROM DUAL UNION ALL
SELECT  203,   20 FROM DUAL UNION ALL
SELECT   20,    2 FROM DUAL;

CREATE TABLE Product ( Cat1, Cat2, Cat3, Cat4, Cat_ID ) AS
SELECT 1, CAST( NULL AS NUMBER ), CAST( NULL AS NUMBER ), CAST( NULL AS NUMBER ), 11 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 202 FROM DUAL UNION ALL
SELECT 1, NULL, NULL, NULL, 112 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 2035 FROM DUAL;

Update:

UPDATE Product p
SET ( cat1, cat2, cat3, cat4 ) = (
          SELECT new_cat1,
                 new_cat2,
                 new_cat3,
                 new_cat4
          FROM   (
            SELECT TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 1 ) ) AS new_cat1,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 2 ) ) AS new_cat2,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 3 ) ) AS new_cat3,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 4 ) ) AS new_cat4,
                   cat_id
            FROM   Category
            START WITH CAT_UST_ID IS NULL
            CONNECT BY CAT_UST_ID = PRIOR CAT_ID
          ) c
          WHERE p.cat_id = c.cat_id
        );

Results:

SELECT * FROM Product;

gives:

      CAT1       CAT2       CAT3       CAT4     CAT_ID
---------- ---------- ---------- ---------- ----------
         1         11                               11 
         2         24        202                   202 
         1         11        112                   112 
         2         20        203       2035       2035

It seems to be OK. http://sqlfiddle.com/#!4/5989b/5/1 Thank u so much MT0 for helpfully answer. — Bazaaran Corbi, Jan 13 '16 at 20:37
I need some explanations about update. I understand that you use 'connect by' to solve hierarchy CATEGORY table, but I couldnt understand why you use these..? SELECT TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 1 ) ) AS new_cat1 — Bazaaran Corbi, Jan 13 '16 at 20:46
If you do: `SELECT SYS_CONNECT_BY_PATH( CAT_ID, ',' ) As path, cat_id FROM Category START WITH CAT_UST_ID IS NULL CONNECT BY CAT_UST_ID = PRIOR CAT_ID;` you will see that `SYS_CONNECT_BY_PATH()` returns a list of numbers representing the `CAT_ID`s back until the root element. `REGEXP_SUBSTR( path, '\d+', 1, n )` will get the nth number in the path (as a string and then `TO_NUMBER` explicitly converts it to a `NUMBER`). — MT0, Jan 13 '16 at 21:34
Why do you need a stored procedure **and** a function? You can just run the update query and it will directly modify the table. If you want a procedure to run the update then just wrap the query in a [procedure statement](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm). — MT0, Jan 13 '16 at 21:38

oracle sql hierarchical queries

1 Answers1