Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Multiple column case/subselect advice

Re: Multiple column case/subselect advice

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 04 Dec 2006 17:31:59 -0800
Message-ID: <1165282312.792752@bubbleator.drizzle.com>


Geoff Muldoon wrote:
> 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

Neat is nice ... but how does it perform and what does the EXPLAIN PLAN look like?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Dec 04 2006 - 19:31:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US