User Tools

Site Tools


kurs:sets_gruppenoperatoren

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
kurs/sets_gruppenoperatoren.1288443620.txt.gz · Last modified: 2014/09/10 21:22 (external edit)