How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?

I have a table with categories and another table with a discount for categories per user:

create table category
( 
  id NUMBER NOT NULL,
  parent_id NUMBER,
  name nvarchar2(255),
  PRIMARY KEY( id)
);

create table category_discount
( 
  user_id NUMBER NOT NULL,
  category_id NUMBER NOT NULL,
  discount NUMBER NOT NULL,
  PRIMARY KEY( user_id, category_id)
);

insert into category( id, parent_id, name ) VALUES( 1, null, 'root');
insert into category( id, parent_id, name ) VALUES( 2, 1, 'C1');
insert into category( id, parent_id, name ) VALUES( 11, 7, 'C11');
insert into category( id, parent_id, name ) VALUES( 12, 7, 'C12');
insert into category( id, parent_id, name ) VALUES( 3, 1, 'C3');
insert into category( id, parent_id, name ) VALUES( 4, 1, 'C4');
insert into category( id, parent_id, name ) VALUES( 5, 4, 'C5');
insert into category( id, parent_id, name ) VALUES( 6, 4, 'C6');
insert into category( id, parent_id, name ) VALUES( 9, 6, 'C9');
insert into category( id, parent_id, name ) VALUES( 10, 6, 'C10');
insert into category( id, parent_id, name ) VALUES( 7, 1, 'C7');

insert into category_discount( user_id, category_id, discount ) VALUES( 1, 1, 30);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 4, 20);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 7, 25);

Now when I do this query:

SELECT category.*, 
       category_discount.user_id, 
       category_discount.discount, 
       LEVEL 
FROM   category
LEFT JOIN category_discount 
       ON category.id = category_discount.category_id 
       AND category_discount.user_id = 1
START WITH parent_id is null
CONNECT BY PRIOR id = parent_id;

...I get this output which is fine:

```
| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
| 1 | (null) | root | 1 | 30 | 1 |
| 2 | 1 | C1 | (null) | (null) | 2 |
| 3 | 1 | C3 | (null) | (null)

with a (id, parent_id, name, user_id, discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as lvl from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id, 
    nvl(cd.discount, a.discount),
    lvl + 1
  from category c2
  LEFT JOIN category_discount cd 
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      20          4
        10          6 C10                     20          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3

Answer to original question:

Assuming the following based on your example data (but I am not sure this is what you want):

There is a discount for the whole branch defined by the root - I call this root_discount.

If a child (c1) has discount set, then that is the discount for that child.

If c2 is a child of c1, and c2 does not have discount set, then c2 inherits the discount of c1.

If c3 is a child of c2, and c3 does not have discount set, then c3 inherits the discount from the root (root_discount). This makes the discount 30 for rows with ID 9,10. Without this, it gets a lot simpler.

with a (id, parent_id, name, user_id, discount, prev_discount, root_discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as prev_discount,
    discount as root_discount, 1 as lvl 
  from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id, 
    nvl(cd.discount, case when prev_discount = 1 then a.discount else root_discount end),
    case when cd.discount is not null then 1 else a.prev_discount - 1 end as prev_discount, 
    root_discount, lvl + 1
  from category c2
  LEFT JOIN category_discount cd
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      30          4
        10          6 C10                     30          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3

StackExchange Database Administrators Q#228188, answer score: 2