Multiple column case/subselect advice

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Mon, 4 Dec 2006 16:54:06 +1100
Message-ID: <MPG.1fde630c1fc061c59898ab@news.readfreenews.net>

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 Received on Sun Dec 03 2006 - 23:54:06 CST