| 5 |
|
with recursive Depts As ( |
| 6 |
|
Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT as VarChar(256)) as DEPT_PATH, |
| 7 |
|
cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH |
| 8 |
< |
From DEPARTMENT Where HEAD_DEPT is NULL |
| 8 |
> |
From DEPARTMENT Where HEAD_DEPT is NULL |
| 9 |
|
UNION ALL |
| 10 |
< |
Select DEPT_NO, DEPARTMENT, HEAD_DEPT, Depts.DEPT_PATH || ' / ' || DEPARTMENT as DEPT_PATH, |
| 11 |
< |
Depts.DEPT_KEY_PATH || ';' || DEPT_NO as DEPT_KEY_PATH |
| 12 |
< |
From DEPARTMENT |
| 13 |
< |
JOIN Depts On HEAD_DEPT = Depts.DEPT_NO |
| 10 |
> |
Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH || ' / ' || D.DEPARTMENT as DEPT_PATH, |
| 11 |
> |
Depts.DEPT_KEY_PATH || ';' || D.DEPT_NO as DEPT_KEY_PATH |
| 12 |
> |
From DEPARTMENT D |
| 13 |
> |
JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO |
| 14 |
|
) |
| 15 |
|
|
| 16 |
< |
Select A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, |
| 16 |
> |
Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, |
| 17 |
|
A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH |
| 18 |
|
From EMPLOYEE A |
| 19 |
|
JOIN Depts D On D.DEPT_NO = A.DEPT_NO; |
