dokuwiki

This is an old revision of the document!

---

 select * from 
(
  select employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME  from employees

    minus

  SELECT  JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln
  FROM JOB_HISTORY , EMPLOYEES 
  WHERE ( (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID) )
) 
--where id = 101


----

select * from (
SELECT 0 sb, 'history', start_date, end_date, JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln, JOB_HISTORY.JOB_ID jid
FROM JOB_HISTORY , EMPLOYEES 
WHERE  (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID)  
and JOB_HISTORY.EMPLOYEE_ID = :b_eid
union
select 1 sb, 'jetzt', null, sysdate, employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME, JOB_ID  from employees 
where employee_id = :b_eid
) order by sb, end_date


select sb, h, start_date, end_date, id, fn, ln, jid, prior jid from (
SELECT 0 sb, 'history' h, start_date, end_date, JOB_HISTORY.EMPLOYEE_ID id, EMPLOYEES.FIRST_NAME fn, EMPLOYEES.LAST_NAME ln, JOB_HISTORY.JOB_ID jid
FROM JOB_HISTORY , EMPLOYEES 
WHERE  (EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID)  
and JOB_HISTORY.EMPLOYEE_ID = 101
union
select 1 sb, 'jetzt', null, sysdate, employee_id, FIRST_NAME, EMPLOYEES.LAST_NAME, JOB_ID  from employees 
where employee_id = 101
) connect by PRIOR employee_id
 by sb, end_date

select employee_id as id, job_id, last_name, to_date(sysdate) from employees
union 
select employee_id, job_id, to_char(null), to_date(end_date) from job_history
order by 1,3,4 desc

select first_name, last_name from employees where employee_id in
(
  select employee_id
  from employees  
  where job_id = 'IT_PROG'
 union
  select employee_id
  from job_history 
  where job_id = 'IT_PROG'
)
------

select first_name, last_name, stand, datum from employees,  
(
  select employee_id, 
         job_id, 
         'jetzt' stand, 
         sysdate datum 
  from employees  
  where job_id = 'IT_PROG'
 union
  select employee_id, 
         job_id, 
         'früher' stand, 
         end_date datum 
  from job_history 
  where job_id = 'IT_PROG'
) history 
where history.employee_id = employees.employee_id

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)