local-employeedb/patches/02-patch.sql


Alter Table EMPLOYEE
  Add Photo Blob;

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, A.PHOTO
From EMPLOYEE A
JOIN Depts D On D.DEPT_NO = A.DEPT_NO;

Update EMPLOYEE Set Photo =:MUGSHOT Where Emp_no = 2;

Update DBVERSIONINFO Set VersionNo = 2;
Commit;

Revision:	37
Committed:	Mon Feb 15 14:44:25 2016 UTC (8 years, 2 months ago) by tony
Content type:	application/sql
File size:	828 byte(s)
Log Message:	Committing updates for Release R1-4-0
#	Content
1
2	Alter Table EMPLOYEE
3	Add Photo Blob;
4
5	Create View DeptList
6	As
7
8	with recursive Depts As (
9	Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT as VarChar(256)) as DEPT_PATH,
10	cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH
11	From DEPARTMENT Where HEAD_DEPT is NULL
12	UNION ALL
13	Select DEPT_NO, DEPARTMENT, HEAD_DEPT, Depts.DEPT_PATH \|\| ' / ' \|\| DEPARTMENT as DEPT_PATH,
14	Depts.DEPT_KEY_PATH \|\| ';' \|\| DEPT_NO as DEPT_KEY_PATH
15	From DEPARTMENT
16	JOIN Depts On HEAD_DEPT = Depts.DEPT_NO
17	)
18
19	Select A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,
20	A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH, A.PHOTO
21	From EMPLOYEE A
22	JOIN Depts D On D.DEPT_NO = A.DEPT_NO;
23
24	Update EMPLOYEE Set Photo =:MUGSHOT Where Emp_no = 2;
25
26	Update DBVERSIONINFO Set VersionNo = 2;
27	Commit;
28