i am needing to create a view on an oracle database. The view is to
be made up from two tables: ps_dept_tbl and ps_dept_tbl_hr.
I need the view to contain the max date, only active departments,
and the description from the ps_dept_tbl_hr. finally, if I have a department that does not reside on both tables, then I need to
exclude it. to get the max date and active departments only, I've
come up with these selects:
The first one is for the ps_dept_tbl:
select * from ps_dept_tbl pdt
where pdt.effdt = (select max(pdt1.effdt) from ps_dept_tbl pdt1
where pdt.deptid = pdt1.deptid)
and pdt.eff_status = 'A'
order by pdt.deptid
The next one for the the ps_dept_tbl_hr:
select * from ps_dept_tbl_hr pdth
where pdth.effdt = (select max(pdth1.effdt) from ps_dept_tbl_hr pdth1
where pdth.deptid = pdth1.deptid)
and pdth.eff_status = 'A'
order by pdth.deptid
this gives me the rows i need, now i just need to consolidate
these into one view and exclude departments that do not exist
on both tables.
does some guru out there know how to write a select to create the
view I need? Thanks in advance