Nested decode statement in ORACLE SQL-CodePudding

I'm writing simple SQL Statement where I need to found my boss's boss. For example if there is three ranks in the column like (lil boss,boss and big boss) and I input lil boss id it need to return the the big boss id. I think that this query must be write white case or decode statements but i have struggles with that. In this code everything would run fine except if I input lil boss ID.Can I use nested decode or case statements? I think that UNION statement would be solution also . If my table has three column:

id	status	boss_id
1	big boss	null
2	big boss	null
3	lil boss	4
4	boss	2

If I input 3 it have to return 2

      select decode(status,'big boss', id,
                           'boss',boss_id,
                           'lil boss',boss_id)
      from bosses
      where id=123113;

CodePudding user response：

I need to found my boss's boss

Use a hierarchical query:

SELECT CONNECT_BY_ROOT id AS root_id,
       CONNECT_BY_ROOT status AS root_status,
       id,
       status
FROM   bosses
WHERE  LEVEL = 3
OR     (LEVEL < 3 AND CONNECT_BY_ISLEAF = 1)
START WITH id = 2
CONNECT BY PRIOR boss_id = id;

Which, for the sample data:

CREATE TABLE bosses (id, status, boss_id) AS
SELECT 1, 'big boss', null FROM DUAL UNION ALL
SELECT 2, 'big boss', null FROM DUAL UNION ALL
SELECT 3, 'lil boss',    4 FROM DUAL UNION ALL
SELECT 4, 'boss',        2 FROM DUAL;

Outputs:

ROOT_ID	ROOT_STATUS	ID	STATUS
3	lil boss	2	big boss

and if you START WITH id = 2:

SELECT CONNECT_BY_ROOT id AS root_id,
       CONNECT_BY_ROOT status AS root_status,
       id,
       status
FROM   bosses
WHERE  LEVEL = 3
OR     (LEVEL < 3 AND CONNECT_BY_ISLEAF = 1)
START WITH id = 2
CONNECT BY PRIOR boss_id = id;

Then it outputs:

ROOT_ID	ROOT_STATUS	ID	STATUS
2	big boss	2	big boss

fiddle

CodePudding user response：

I was able to come to a solution to this problem. For this purpose I used hierarchical queries and the solution of @MT0. Here is the code that not need to use level. I think it is the best and simpliest solution to the problem. Thanks for helping me!

    select id as bossid,
           status as boss_status
    from   bosses b
    where b.status='big boss'
    start with id ='4'
    connect by prior boss_id = id;