Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Location
    Kansas city
    Posts
    1

    Question Unanswered: newbie oracle sql help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    define a view for each of those queries, except i would advise you not to use SELECT *, but rather, list the individual columns, and maybe even give them nice aliases

    then define your final view as an inner join of those two views

    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •