Differences

This shows you the differences between two versions of the page.

--- kurs:sets_gruppenoperatoren [2010/10/30 15:00]
mh
+++ kurs:sets_gruppenoperatoren [2012/09/25 17:22]
127.0.0.1 external edit
@@ Line 75: / Line 75: @@
 ) history
 where history.employee_id = employees.employee_id
+</code>
+<code>
+with history_union_employees as
+(
+SELECT 1 h, EMPLOYEES.FIRST_NAME,
+       EMPLOYEES.LAST_NAME,
+       JOB_HISTORY.START_DATE,
+       JOB_HISTORY.END_DATE,
+       JOB_HISTORY.JOB_ID
+FROM JOB_HISTORY , EMPLOYEES
+WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
+union
+select 2 h, EMPLOYEES.FIRST_NAME,
+       EMPLOYEES.LAST_NAME,
+       null,
+       null,
+       job_id
+from employees where employee_id in (select employee_id from job_history)
+)
+select first_name, last_name, start_date, end_date, job_title from
+ history_union_employees h join
+ jobs j on h.job_id = j.job_id
+order by last_name, nvl(end_date,sysdate)
 </code>

dokuwiki