Re: PL/SQL challenge

From: Haakon T. Soenderland <hts_at_sasdata.no>
Date: 1995/06/27
Message-ID: <3sp3nv$4mr_at_news.dax.net>#1/1

In article <3snvcm$1vm_at_lastactionhero.rs.itd.umich.edu>, ab831_at_detroit.freenet.org says...
>
>Hi all,
>
>I don't know if this will really be a challenge for some of the
>geniuses you encounter in this group, but maybe...
>
>Situation:
>
>Two tables, one key field, one to many relationship.
>
>Sample structure:
>
>ORDER ORDER_CHANGE
>------ -----------
>ORDER_ID; ORDER_ID
>CUSTOMER_ID; ORDER_STATUS
>SHIP_TO_ID: CHANGE_DATE
>... GRIEF_CODE
>... ...
>
[snip]

>Grab a set of fields from the ORDER table as well as the *first* date
 where
>the GRIEF_CODE meets a certain criteria (e.g., is equal to some string)
 for
>that particular order in question.
>
>Any ideas?

Is something like this what you're looking for?

select o.order_id, o.customer_id, o.ship_to_id, oc.change_date   from order o, order_change oc
 where o.order_id = oc.order_id
   and oc.change_date = (select min(oc2.change_date)

                           from order_change oc2
                          where grief_code = 'SOMETHING'
                             and oc2.order_id = o.order_id)

   and oc.grief_code = 'SOMETHING'
/

I haven't gone through and checked this (it's going to be _so_ embarassing if this turns out to be wrong), but I think that this should work If you need a cursor, it should be no problem turning this into one:

DECLARE
 cursor order_Grief_n_Stuff (p_grief_code varchar2) is    select o.order_id, o.customer_id, o.ship_to_id, oc.change_date      from order o, order_change oc
    where o.order_id = oc.order_id

      and oc.change_date = (select min(oc2.change_date) 
                              from order_change oc2
                             where grief_code = p_grief_code
                               and oc2.order_id = o.order_id)
      and oc.grief_code = p_grief_code;

....

Hope this helps.

Haakon

-- 
hts_at_sasdata.no | haakon.soenderland_at_thcave.bbs.no | Haakon T. Soenderland
Scandinavian Airlines Data Norway A/S  
---
"40 skiver og et herpa anlegg, en leilighet som trenger aa spyles. Ingen 
 venner og ingen penger, alt jeg har er mine klamme hender.." Jokke '94

Received on Tue Jun 27 1995 - 00:00:00 CEST