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