Geoff Muldoon wrote:
> Hi all,
>
> Oracle 10g RAC on Linux ..
>
> I have a situation with two tables, one of which holds information about
> an item, and the other which holds historical information about it.
>
> pseudo code/description in a hopefully understandable form:
>
> 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?
>
> Geoff M
Show us what you've done.
--
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 - 10:32:46 CST
