PL/SQL using outer join

Question

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)
  and parameter (client_ref_ integer).

I need to select data from table A with condition:
1) if there are rows in table B connected with client_ref (if query

select b.t_ref from B b where b.client_ref = client_ref_ 

returns any data), my query will look like this:

select max(l_date) from A where t_ref in (select b.t_ref from B b where b.client_ref = client_ref_)

2) if the query above returns any data, my query will look like this:

 select max(l_date) from A where t_ref in (select c.t_id from C c where c.client_ref = client_ref_)

Now I've written a PLSQL function:

  select max(aa.l_date) into l_date from A aa where aa.t_ref in (select bb.t_ref from B bb where bb.client_ref = client_ref_);
  if l_date is null then
    select max(aa.l_date) into l_date from A aa where t_ref in (select t_id from C c where c.client_ref = client_ref_);
  end if;
return l_date;

It works, but it isn't good idea, because I call table A 2 times. Is it possible to avoid second call, and do this in one query?

Answer 1

with
     prep ( t_ref, idx ) as (
       select  b.t_ref, 1
         from  table_B b 
         where b.client_ref = :client_ref 
       union all
       select  c.t_ref, 2
         from  table_C c 
         where c.client_ref = :client_ref 
     )
select max(a.l_date) keep (dense_rank first order by p.idx) -- into l_date
from   table_A a inner join prep p
                 on a.t_ref = p.t_ref
;

Explanation: First scan table_B for rows with matching client_ref; if any are found, collect them and attach an "index" of 1. Then scan table_C and do the same, but with index 2. (If table_C is very large, this is wasted time when :client_ref was found in table_B; if that is a concern, it can be addressed with a little more code). Then table_A is joined to the result of putting these two sets of rows together. keep dense_rank first... will make sure only rows withidx = 1 are considered for max(l_date), but if no such rows exist only the rows with idx = 2 will be considered.

If :client_ref is not found in either of tables B and C, the resulting max(l_date) will be null.

I wrote the query with :clent_ref as a bind variable so I could test the query; you may change it to your variable name client_ref_

Answer 2

You can use this way : I covered all your case here. Please verify

create  table A (t_ref integer, l_date date);

create   table B (t_ref integer, client_ref integer);

create   table C (t_id integer, client_ref integer);



 SQL> select * from a
  2  /

     T_REF L_DATE
---------- ---------
        10 01-JAN-11
        20 02-FEB-11
        30 02-MAR-11

SQL> select * from b;

     T_REF CLIENT_REF
---------- ----------
        10        101
        20        102

SQL> select * from c;

      T_ID CLIENT_REF
---------- ----------
        10        101
        20        102
        30        101
        40        103




sql> select max(aa.l_date) 
    --into l_date 
    from A aa 
    where aa.t_ref =any(   case when ( select bb.t_ref from B bb where bb.client_ref = '101' and rownum <2 ) is null then
                                     ( select t_id from C c where c.client_ref = '101' )
                                     when  ( select t_id from C c where c.client_ref = '101' and rownum <2 ) is not null then
                                      ( select t_id from C c where c.client_ref = '101' and rownum <2   )
                                      else
                                     ( select bb.t_ref from B bb where bb.client_ref = '101' )
                                     end
                         );

MAX(AA.L_
---------
01-JAN-11



SQL> select max(aa.l_date) 
--into l_date 
from A aa 
where aa.t_ref =any(   case when ( select bb.t_ref from B bb where bb.client_ref = '102' and rownum <2 ) is null then
                                 ( select t_id from C c where c.client_ref = '102' )
                                 when  ( select t_id from C c where c.client_ref = '102' and rownum <2 ) is not null then
                                      ( select t_id from C c where c.client_ref = '102'   )
                                  else
                                 ( select bb.t_ref from B bb where bb.client_ref = '102' )
                                     end
                     );

MAX(AA.L_
---------
02-FEB-11


SQL> select max(aa.l_date) 
--into l_date 
from A aa 
where aa.t_ref =any(   case when ( select bb.t_ref from B bb where bb.client_ref = '103' and rownum <2 ) is null then
                                 ( select t_id from C c where c.client_ref = '103' )
                                 when  ( select t_id from C c where c.client_ref = '103' and rownum <2 ) is not null then
                                  ( select t_id from C c where c.client_ref = '103'  )
                                  else
                                 ( select bb.t_ref from B bb where bb.client_ref = '103' )
                                     end
                     );

MAX(AA.L_
---------


SQL> 

Answer 3

Try this.

select max(aa.l_date) into l_date from A aa 
    where t_ref in 
    (SELECT t_ref from (select t_id t_ref,client_ref_ client_ref_ from C c
                          UNION
                        select t_ref t_ref,client_ref_ client_ref_ from B b) 
                       tmp where tmp.client_ref = client_ref_)
return l_date;