I have data like:
file_id | file_name | user_group_id | group_permission | file_parent
1 abc devs read NULL
1 abc devs write NULL
2 def NULL NULL 1
3 jkl NULL NULL 2
3 ghi end_u read 2
and it is in a sort of hierarchy tree where files has a user group and permission attached, the data that I have doesn't always have user group and permissions attached, if this is the case then they should inherit the user group and permission of their parent (some times it may be a super parent, i.e., the parent of a parent). I want to build the output like below but I can't use recursion
file_id | file_name | user_group_id | group_permission | file_parent
1 abc devs read NULL
1 abc devs write NULL
2 def devs read 1
2 def devs write 1
3 jkl devs read 2
3 jkl devs write 2
4 ghi end_u read 2
I tried to look for a solution and it seems that Oracle has the "connect by prior" structure that can help but I am quite new to database development and don't know how to build this.
I have checked these pages but I can't yet visualize a solution:
You can use:
SELECT file_id,
file_name,
CONNECT_BY_ROOT(user_group_id) AS user_group_id,
CONNECT_BY_ROOT(group_permission) AS group_permission,
file_parent
FROM table_name
START WITH
user_group_id IS NOT NULL
AND group_permission IS NOT NULL
CONNECT BY
PRIOR file_id = file_parent
AND user_group_id IS NULL
AND group_permission IS NULL
ORDER BY
file_id,
file_name,
group_permission
Which, for your sample data:
CREATE TABLE table_name ( file_id, file_name, user_group_id, group_permission, file_parent ) AS
SELECT 1, 'abc', 'devs', 'read', NULL FROM DUAL UNION ALL
SELECT 1, 'abc', 'devs', 'write', NULL FROM DUAL UNION ALL
SELECT 2, 'def', NULL, NULL, 1 FROM DUAL UNION ALL
SELECT 3, 'jkl', NULL, NULL, 2 FROM DUAL UNION ALL
SELECT 4, 'ghi', 'end_u', 'read', 2 FROM DUAL;
Outputs:
FILE_ID FILE_NAME USER_GROUP_ID GROUP_PERMISSION FILE_PARENT 1 abc devs read 1 abc devs write 2 def devs read 1 2 def devs write 1 3 jkl devs read 2 3 jkl devs write 2 4 ghi end_u read 2
db<>fiddle here