Geoff Muldoon wrote:
> damorgan_at_psoug.org says...
>
>>> Table ITEM_INFO
>>> ITEM_ID number (primary key),
>>> ITEM_STAGE varchar2,
>>> ITEM_STATUS varchar2,
>>> EFFECT_DATE date,
>>> .... (plus about 10 more columns - about 1.2 million rows)
>>>
>>> Table ITEM_STATUS_HIST
>>> ITEM_ID number,
>>> HIST_NO number,
>>> ITEM_STAGE varchar2,
>>> ITEM_STATUS varchar2,
>>> EFFECT_DATE date
>>> (no other columns - about 5 million rows)
>>>
>>> Primary key is combo of ITEM_ID/HIST_NO.
>>>
>>> For each ITEM_ID the HIST_NO values in the ITEM_STATUS_HIST
>>> table should start at 1 and increment upwards by 1, but gaps can appear.
>>>
>>> The maximum HIST_NO for an ITEM_ID in the ITEM_STATUS_HIST table will
>>> contain the current values of that particular subset of columns from the
>>> ITEM_INFO table.
>>>
>>> In the majority of cases I simply want to select all the information from
>>> the ITEM_INFO table.
>>>
>>> However if the ITEM_STATUS is a particular value (PL), then I need to look
>>> at the immediately prior (if existing) HIST_NO row for that ITEM_ID and
>>> *only if* the ITEM_STATUS for that row is another particular value (WD)
>>> then I need to use the values in that prior row for the three columns
>>> ITEM_STAGE, ITEM_STATUS, and EFFECT_DATE from the ITEM_STATUS_HIST table
>>> instead of the current values from ITEM_INFO, plus all the other columns
>>> in the ITEM_INFO table.
>>>
>>> I can work the basic logic to do the multiple case statements, determining
>>> when I need to substitute the values from the ITEM_STATUS_HIST table, but
>>> don't know of any more efficient way of applying it so that I can do a
>>> multi-column subselect instead of applying that confounded logic three
>>> times, once for each of the affected columns.
>>>
>>> Any hints please?
>
>> Show us what you've done.
>
> As mentioned, I have a *rough* working solution, but am looking for a more
> efficient technique, one that ideally won't need to use the case statement
> and nested sub-selects three times, once for each of the affected columns.
>
> Current work:
>
> select a.ITEM_ID,
> nvl((case when a.ITEM_STATUS = 'PL' then --first condition met
> (select b.ITEM_STAGE --get value from history table instead
> from ITEM_STATUS_HIST b
> where b.ITEM_ID = a.ITEM_ID
> and b.HIST_NO = --pick row in history table
> (select max(c.HIST_NO) --get the max ...
> from ITEM_STATUS_HIST c
> where c.ITEM_ID = a.ITEM_ID
> and c.HIST_NO < --... that is less that the last
> (select max(d.HIST_NO)
> from ITEM_STATUS_HIST d
> where d.ITEM_ID = a.ITEM_ID))
> and b.ITEM_STATUS = 'WD') --second condition, else will be null
> else null end), --else first condition not met, set as null
> a.ITEM_STAGE) --nvl value if either condition not met
> as ITEM_STAGE--,
> --<repeat above logic for ITEM_STATUS and EFFECT_DATE columns>,
> --a.<other columns>
> from ITEM_INFO a
>
> Why am I doing this? It is a change to part of a data warehouse ETL
> stored proc, necessitated by an application script <cough/splutter>
> "enhancement".
>
> Geoff M
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.
--
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 - 17:32:49 CST
