title: Need inner and outer join in one function
body: <p>I have one table that holds a record for each customer (main table). I then have a table with additional detail for some customers. The additional detail table sometimes has no records for a record in the main table. Sometimes the detail table has multiple records for a record in the main table &amp; if this is the case I need the most recent record (hence the max subselect). </p>
  
  <p>The trouble is my function only returns values for the few records in the detail table. If I comment out the portion of the function that looks at the detail table and just return the <code>STAT3</code> value it seems to work. How do I make the second select statment below only apply if there is a result for that query?</p>
  
  <pre><code>create or replace FUNCTION           "F_RETURN_STAT" (
  N_UNIQUE IN NUMBER)
  RETURN VARCHAR2
  IS
  V_STAT3 varchar2(20);
  V_STAT varchar2(20);
  V_STAT2 varchar2(20);
  D_ACTDATE date;
  D_STARTDATE date;
  
  BEGIN
  select expire into D_ACTDATE
  from main_table a
  where a.uniquefield = N_UNIQUE;
  IF 
  D_ACTDATE &gt; SYSDATE
  or 
  D_ACTDATE is null
  then
  V_STAT :='TRUE';
  else 
  v_STAT :='FALSE';
  end if;
  
  
  select b.startdate into D_STARTDATE
  from main_table a, detail_table b
  where a.uniquefield= b.main_table_id(+) and
  b.main_table_id = N_UNIQUE and
  b.uniquefield in 
    (select max(c.uniquefield) from detail_table c  group by main_table_id);
  if 
  D_STARTDATE is not null
   then
  V_STAT2 :='FALSE';
    end if;
  
      if 
   V_STAT2 ='FALSE'
          then
          V_STAT3 :='FALSE';
          ELSE
   V_STAT3 := V_STAT;
          end if ;
  RETURN(V_STAT3);
  end;
  </code></pre>