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