Hi All,
I use Oracle 19c. I have two qestions. First, is there any easy way to find parent id without using scalar subquery?
WITH
Q1 AS (
SELECT 1 ID, 'A1' NODE, NULL PARENT_NODE FROM DUAL
UNION ALL
SELECT 2, 'B1', 'A1' FROM DUAL
UNION ALL
SELECT 3, 'B2', 'A1' FROM DUAL
UNION ALL
SELECT 4, 'C1', 'B1' FROM DUAL
UNION ALL
SELECT 5, 'C2', 'B2' FROM DUAL
UNION ALL
SELECT 6, 'C3', 'B1' FROM DUAL
)
SELECT
ID,
NODE,
PARENT_NODE,
(SELECT T2.ID FROM Q1 T2 WHERE T2.NODE = T1.PARENT_NODE) PARENT_ID
FROM Q1 T1 ORDER BY 1;
Second, is there any solution to calculate SALARY_NEW column also without scalar subquery?
WITH
Q1 AS (
SELECT 1 ID, 'A1' NODE, NULL PARENT_NODE, 1000 salary, 'F' C_TYPE FROM DUAL
UNION ALL
SELECT 2, 'B1', 'A1', 450, 'C' FROM DUAL
UNION ALL
SELECT 3, 'B2', 'A1', 700, 'C' FROM DUAL
UNION ALL
SELECT 4, 'C1', 'B1', 250, 'F' FROM DUAL
UNION ALL
SELECT 5, 'C2', 'B2', 350, 'C' FROM DUAL
UNION ALL
SELECT 6, 'C3', 'B1', 200, 'F' FROM DUAL
),
Q2 AS (
SELECT
ID,
NODE,
PARENT_NODE,
(SELECT T2.ID FROM Q1 T2 WHERE T2.NODE = T1.PARENT_NODE) PARENT_ID,
SALARY,
C_TYPE
FROM Q1 T1
)
SELECT
V.*,
CASE WHEN C_TYPE = 'F' THEN V.SALARY + ( ID = PRIOR PARENT_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) END SALARY_NEW
FROM Q2 V
ORDER BY ID;
Thanks