ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/examples/scriptengine/tests/DeptListView.sql
Revision: 47
Committed: Mon Jan 9 15:31:51 2017 UTC (7 years, 2 months ago) by tony
Content type: application/sql
File size: 758 byte(s)
Log Message:
Committing updates for Release R2-0-1

File Contents

# Content
1 Set AUTODDL off;
2 Create View DeptList
3 As
4
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
9 UNION ALL
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 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;
20 set autoddl on;
21 Commit;
22 select * from DeptList;