Re: Multiple column case/subselect advice

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Tue, 5 Dec 2006 11:16:29 +1100
Message-ID: <MPG.1fdf6568ec1ef1c19898af@news.readfreenews.net>

damorgan_at_psoug.org says...
>
> If you are, as it appears, repeating the same subquery multiple times
> look into using the WITH CLAUSE:
> http://www.psoug.org/reference/with.html
> as one possible solution.

Rough version 2:

with HIST_VALUES as (

    select a.ITEM_ID, b.ITEM_STAGE, b.ITEM_STATUS, b.EFFECT_DATE     from ITEM_INFO a, ITEM_STATUS_HIST b     where b.ITEM_ID = a.ITEM_ID
    and a.ITEM_STATUS = 'PL'
    and b.HIST_NO =

        (select max(c.HIST_NO)
         from ITEM_STATUS_HIST c
         where c.ITEM_ID = a.ITEM_ID
         and c.HIST_NO < 
             (select max(d.HIST_NO)
              from ITEM_STATUS_HIST d
              where d.ITEM_ID = a.ITEM_ID))

    and b.ITEM_STATUS = 'WD')
select e.ITEM_ID,

nvl(f.ITEM_STAGE, e.ITEM_STAGE) as ITEM_STAGE,
nvl(f.ITEM_STATUS, e.ITEM_STATUS) as ITEM_STATUS,
nvl(f.EFFECT_DATE, e.EFFECT_DATE) as EFFECT_DATE--,

--e.<other columns>
from ITEM_INFO e, HIST_VALUES f
where e.ITEM_ID = f.ITEM_ID(+)

Much neater, thanks Daniel. Any clues on further improvements, particularly in the query in the WITH clause?

Geoff M Received on Mon Dec 04 2006 - 18:16:29 CST