Re: Multiple column case/subselect advice

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Tue, 5 Dec 2006 10:18:24 +1100
Message-ID: <MPG.1fdf57cf69aa4b829898ac@news.readfreenews.net>

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 Received on Mon Dec 04 2006 - 17:18:24 CST