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 D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  ' / ' || D.DEPARTMENT as DEPT_PATH,
Depts.DEPT_KEY_PATH || ';' || D.DEPT_NO as DEPT_KEY_PATH
From DEPARTMENT  D
JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO
)

Select distinct 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:	47
Committed:	Mon Jan 9 15:31:51 2017 UTC (7 years, 3 months ago) by tony
Content type:	application/sql
File size:	758 byte(s)
Log Message:	Committing updates for Release R2-0-1
#	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	tony	47	From DEPARTMENT Where HEAD_DEPT is NULL
9	tony	37	UNION ALL
10	tony	47	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	tony	37	)
15
16	tony	47	Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,
17	tony	37	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;