Salary Accumulation by mgr (DB2 for Oracle CONNECT_BY)

Question

We have data from scott.emp table:

select empno, ename,mgr, sal from emp order by empno ;

EMPNO|ENAME     |  MGR|       SAL
-----|----------|-----|----------
 7369|SMITH     | 7902|       800
 7499|ALLEN     | 7698|      1600
 7521|WARD      | 7698|      1250
 7566|JONES     | 7839|      2975
 7654|MARTIN    | 7698|      1250
 7698|BLAKE     | 7839|      2850
 7782|CLARK     | 7839|      2450
 7788|SCOTT     | 7566|      3000
 7839|KING      |     |      5000
 7844|TURNER    | 7698|      1500
 7876|ADAMS     | 7788|      1100
 7900|JAMES     | 7698|       950
 7902|FORD      | 7566|      3000
 7934|MILLER    | 7782|      1300

14 rows selected.

Then I want to list hirearchy of emp as well as Salary Accumulation, which returns rows like this:

DESKRIPSI | EMPNO|   MGR|  AMOUNT
----------|------|------|--------
KING      |  7839|      |  29.025
.JONES    |  7566|  7839|  10.875
..SCOTT   |  7788|  7566|   4.100
...ADAMS  |  7876|  7788|   1.100
..FORD    |  7902|  7566|   3.800
...SMITH  |  7369|  7902|     800
.BLAKE    |  7698|  7839|   9.400
..ALLEN   |  7499|  7698|   1.600
..WARD    |  7521|  7698|   1.250
..MARTIN  |  7654|  7698|   1.250
..TURNER  |  7844|  7698|   1.500
..JAMES   |  7900|  7698|     950
.CLARK    |  7782|  7839|   3.750
..MILLER  |  7934|  7782|   1.300

14 rows selected.

With Oracle RDBMS, the approach is like this:

WITH pohon
     AS (SELECT
           DISTINCT CONNECT_BY_ROOT empno parent_id, empno AS id
         FROM
           emp
         CONNECT BY
           PRIOR empno = mgr),
     trx
     AS (SELECT
           pohon.parent_id, SUM (tx.sal) AS amount
         FROM
           pohon JOIN emp tx ON pohon.id = tx.empno
         GROUP BY
           pohon.parent_id)
SELECT
  LPAD (r0.ename, LENGTH (r0.ename) + LEVEL * 1 - 1, '.') AS deskripsi,empno, mgr,
  trx.amount
FROM
  emp r0 JOIN trx ON r0.empno = trx.parent_id
START WITH
  r0.mgr IS NULL
CONNECT BY
  r0.mgr = PRIOR r0.empno
;

How can I get the same result in DB2 RDBMS?

Regards.

score 1 · Answer 1 · edited Nov 19 '19 at 15:32

There are two ways, either enable the Oracle-compatible CONNECT_BY clause

or use SQL Standard recursive SQL as per the example on this page

Example 2: Summarized explosion The second example is a summarized explosion. The question posed here is, what is the total quantity of each part required to build part '01'. The main difference from the single level explosion is the requirement to aggregate the quantities. The first example indicates the quantity of subparts required for the part whenever it is required. It does not indicate how many of the subparts are needed to build part '01'.

WITH RPL (PART, SUBPART, QUANTITY) AS
   (
      SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
       FROM PARTLIST ROOT
       WHERE ROOT.PART = '01'
    UNION ALL
      SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
       FROM RPL PARENT, PARTLIST CHILD
       WHERE PARENT.SUBPART = CHILD.PART
   )
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
 FROM RPL
  GROUP BY PART, SUBPART
  ORDER BY PART, SUBPART;

score 1 · Answer 2 · answered Nov 19 '19 at 17:37

The following statement returns the results desired. Run it as is.

WITH EMP (EMPNO, ENAME, MGR, SAL) AS
(
VALUES
  (7369, 'SMITH     ', 7902,  800)
, (7499, 'ALLEN     ', 7698, 1600)
, (7521, 'WARD      ', 7698, 1250)
, (7566, 'JONES     ', 7839, 2975)
, (7654, 'MARTIN    ', 7698, 1250)
, (7698, 'BLAKE     ', 7839, 2850)
, (7782, 'CLARK     ', 7839, 2450)
, (7788, 'SCOTT     ', 7566, 3000)
, (7839, 'KING      ', NULL, 5000)
, (7844, 'TURNER    ', 7698, 1500)
, (7876, 'ADAMS     ', 7788, 1100)
, (7900, 'JAMES     ', 7698,  950)
, (7902, 'FORD      ', 7566, 3000)
, (7934, 'MILLER    ', 7782, 1300)
)
, C (LVL, EMPNO, MGR, ENAME, SAL, CHAIN) AS 
(
SELECT
  0 AS LVL, EMPNO, MGR, ENAME, SAL
, CAST('|' || TRIM(CHAR(EMPNO)) || '|' AS VARCHAR(1024)) AS CHAIN
FROM EMP C 
WHERE NOT EXISTS (SELECT 1 FROM EMP P WHERE P.EMPNO = C.MGR)
  UNION ALL
SELECT C.LVL + 1 AS LVL, E.EMPNO, E.MGR, E.ENAME, E.SAL
, C.CHAIN || TRIM(CHAR(E.EMPNO)) || '|' AS CHAIN
FROM C, EMP E
WHERE E.MGR = C.EMPNO
)
SELECT 
  REPEAT('.', LVL) || ENAME AS DESKRIPSI
, EMPNO
, MGR
, (SELECT SUM(SAL) FROM C C2 WHERE C2.CHAIN LIKE C1.CHAIN || '%') AS AMOUNT
FROM C C1
ORDER BY CHAIN;

Salary Accumulation by mgr (DB2 for Oracle CONNECT_BY)

2 Answers2