oracle - PL/SQL using outer join -

i have 3 tables:

• a (t_ref integer, l_date date),
• b (t_ref integer, client_ref integer),
• c (t_id integer, client_ref integer)
  , parameter (client_ref_ integer).

i need select data table condition:
1) if there rows in table b connected client_ref (if query

select b.t_ref b b b.client_ref = client_ref_  

returns data), query this:

select max(l_date) t_ref in (select b.t_ref b b b.client_ref = client_ref_) 

2) if query above returns data, query this:

 select max(l_date) t_ref in (select c.t_id c c c.client_ref = client_ref_) 

now i've written plsql function:

  select max(aa.l_date) l_date aa aa.t_ref in (select bb.t_ref b bb bb.client_ref = client_ref_);   if l_date null     select max(aa.l_date) l_date aa t_ref in (select t_id c c c.client_ref = client_ref_);   end if; return l_date; 

it works, isn't idea, because call table 2 times. possible avoid second call, , in 1 query?

with      prep ( t_ref, idx ) (        select  b.t_ref, 1           table_b b           b.client_ref = :client_ref         union        select  c.t_ref, 2           table_c c           c.client_ref = :client_ref       ) select max(a.l_date) keep (dense_rank first order p.idx) -- l_date   table_a inner join prep p                  on a.t_ref = p.t_ref ; 

explanation: first scan table_b rows matching client_ref; if found, collect them , attach "index" of 1. scan table_c , same, index 2. (if table_c large, wasted time when :client_ref found in table_b; if concern, can addressed little more code). table_a joined result of putting these 2 sets of rows together. keep dense_rank first... make sure rows withidx = 1 considered max(l_date), if no such rows exist rows idx = 2 considered.

if :client_ref not found in either of tables b , c, resulting max(l_date) null.

i wrote query :clent_ref bind variable test query; may change variable name client_ref_