ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/examples/scriptengine/tests/DeptListView.sql
Revision: 37
Committed: Mon Feb 15 14:44:25 2016 UTC (8 years, 1 month ago) by tony
Content type: application/sql
File size: 738 byte(s)
Log Message:
Committing updates for Release R1-4-0

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 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
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,
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;