scriptengine/tests/DeptListView.sql

Set AUTODDL off;
Create View DeptList
As

with recursive Depts As (
Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH,
cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH
From DEPARTMENT Where HEAD_DEPT is NULL 
UNION ALL
Select DEPT_NO, DEPARTMENT, HEAD_DEPT, Depts.DEPT_PATH ||  ' / ' || DEPARTMENT as DEPT_PATH,
Depts.DEPT_KEY_PATH || ';' || DEPT_NO as DEPT_KEY_PATH
From DEPARTMENT  
JOIN Depts On HEAD_DEPT = Depts.DEPT_NO
)

Select A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, 
A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH
From EMPLOYEE A
JOIN Depts D On D.DEPT_NO = A.DEPT_NO;
set autoddl on;
Commit;
select * from DeptList;
Revision:	37
Committed:	Mon Feb 15 14:44:25 2016 UTC (8 years, 9 months ago) by tony
Content type:	application/sql
File size:	738 byte(s)
Log Message:	Committing updates for Release R1-4-0
#	User	Rev	Content
1	tony	37	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;