Oracle Join 2 views with 5 conditions

Question

i have to join 2 views view_a and view_b.
view_a has columns id,address1,address2,city,state,cntry view_b id,frst_name,last_name,type,date,job_title

desired result
id,Name,address1,address2,city,state,cntry,job_title

Conditions for my query are:
1. join both views on id column.
2. order by date desc
3. concatenate first_name and last_name
4. type equals to "officer"
5. If there are more than one officer then yield only one officer i.e., one top row based on the date.
6. If there is no officer then have null value for the name and job_title column in the result.

Query I have used:

   select 
       * 
   from  
      view_a A 
   join 
   (
          select 
              (first_name || ' ' || last_name) as name,
              job_title,
              id 
          from 
              view_b 
                   where 
              type = 'officer' 
                    and 
               id is not null 
            order by date desc fetch first 1 row only
   ) B
   on A.id=B.id  

But this query is yielding only one result. I'm using Oracle 12c. there are about 800K records in these views.

Answer 1

You can do this:

select id,
    name,
    address1,
    address2,
    city,
    state,
    cntry,
    job_title
(select 
    a.id,
    nvl2(nvl(b.first_name, b.last_name),b.first_name||' '||b.last_name,null) Name,
    a.address1,
    a.address2,
    a.city,
    a.state,
    a.cntry,
    b.job_title,
    a.date
    row_number() over (partition by a.id order by a.date desc nulls last) rn
from  
    view_a a left outer join
    view_b b
on a.id = b.id
and b.type = 'officer')
where rn = 1
order by date desc nulls last;

Answer 2

The following also solved the problem:

SELECT *
FROM view_a a
   LEFT JOIN (SELECT name, job_title, id
           FROM (SELECT (first_name || ' ' || last_name) AS name,
                        job_title,
                        id,
                        ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC) rn
                   FROM view_b
                  WHERE TYPE = 'officer' AND id IS NOT NULL)
          WHERE rn = 1) b
       ON a.id = b.id