SQL join optimalization (get rid of UNION)

Question

Hi,

1st disclaimers:

1. I'm not a programmer, never was
2. had never been taught "higher" math
3. despite the upper statements sometimes I have to work with SQL.

Now I need to create a view from a select of my colleagues (who had used four unions looked like he do not know how to use or in the where part...), and now I'm here.

Is there a simple readable way of getting rid of the last UNION while getting the same result set?

Thanks in advance!

select a.prodt_cde,
      b.ccy,
      a.int_tabno,
      b.start_dn,
      b.end_dn,
      b.frte_term,
      b.base_id,
      b.ptvar,
      c.base_rate,
      c.desc_shnm,
      c.rel_day
 from linc.systwodb_ptico a, linc.systwodb_ptlfo b, linc.systwodb_baso c
 where a.prodt_cde in
      (select prodt_cde
         from linc.systwodb_ptmao
        where prodt_clas in (select prod_clas
                               from linc.systwodb_ramto
                              where main_type in (71, 72))
          and allow_dif in ('Y', 'M'))
  and a.int_type = 'LS'
  and a.int_tabno = b.int_tabno
  and b.ccy in
      (select ccy from linc.systwodb_ptmao where prodt_cde = a.prodt_cde)
  and b.base_id <> 0
  and b.base_id = c.base_id
  and b.ccy = c.ccy
  and ((b.end_dn = 0 and b.start_dn <= c.rel_day) or
      (b.end_dn <> 0 and b.start_dn <= c.rel_day and
      b.end_dn >= c.rel_day) or
      (b.start_dn > c.rel_day and not exists
       (select *
           from linc.systwodb_baso
          where base_id = b.base_id
            and ccy = b.ccy
            and rel_day = b.start_dn) and
       c.rel_day = (select NVL(max(rel_day), 0)
                       from linc.systwodb_baso
                      where base_id = b.base_id
                        and ccy = b.ccy
                        and rel_day < b.start_dn)))
-- 4. PTLFO.BASE_ID = 0, or cannot find BASO before PTLFO.START_DN 
union
select a.prodt_cde,
      b.ccy,
      a.int_tabno,
      b.start_dn,
      b.end_dn,
      b.frte_term,
      b.base_id,
      b.ptvar,
      0 as base_rate,
      ' ' as desc_shnm,
      0 as rel_day
 from linc.systwodb_ptico a, linc.systwodb_ptlfo b --, linc.systwodb_baso c
 where a.prodt_cde in
      (select prodt_cde
         from linc.systwodb_ptmao
        where prodt_clas in (select prod_clas
                               from linc.systwodb_ramto
                              where main_type in (71, 72))
          and allow_dif in ('Y', 'M'))
  and a.int_type = 'LS'
  and a.int_tabno = b.int_tabno
  and b.ccy in
      (select ccy from linc.systwodb_ptmao where prodt_cde = a.prodt_cde)
  and (b.base_id = 0 or not exists
       (select *
          from linc.systwodb_baso
         where base_id = b.base_id
           and ccy = b.ccy
           and rel_day <= b.start_dn))
;

Answer 1

Could you post a rough description of what this is supposed to to do? This query is very difficult to work with without knowing what it's supposed to be doing though. The basic approach to combining these will be to use explicit joins in the from clause like so:

 from
    linc.systwodb_ptico a
    INNER JOIN linc.systwodb_ptlfo b ON a.int_tabno = b.int_tabno
    LEFT OUTER JOIN linc.systwodb_baso c ON -- some kind of horrible mess here

Note the left outer join for systwodb_baso. That's the key point for eliminating the other query. That will ensure there is a row in the result set even if there is no matching record from systwodb_baso.

Update:

In order to eliminate null values from the outer join, use the COALESCE function:

select a.prodt_cde,
      b.ccy,
      a.int_tabno,
      b.start_dn,
      b.end_dn,
      b.frte_term,
      b.base_id,
      b.ptvar,
      COALESCE(c.base_rate, 0) AS base_rate,
      COALESCE(c.desc_shnm, ' ') AS desc_shnm,
      COALESCE(c.rel_day, 0) AS rel_day