Re: PL/SQL challenge

In article <3snvcm$1vm_at_lastactionhero.rs.itd.umich.edu> ab831_at_detroit.freenet.org writes:

> 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
> ... ...
>
> What occurs is an order has a variety of statuses (e.g., entered,
> initiated, backordered, stopped, etc.) and has a date associated with
> each of these statuses (i.e., when a change in status occurs the date is
> recorded). It is also possible for an order to be stopped, resumed,
> and stopped again. What I need to do is write a little snippet that will
> do the following:
>
> 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.

This is easy in SQL and outright trivial PL/SQL...

In SQL:

	SELECT o.order_id, o.customer_id, o.ship_to_id, oc.change_date
	FROM   order o, order_change oc
	WHERE  oc.order_id = o.order_id
	AND    o.order_id = :order_id
	AND    oc.grief_code = :grief_code
	AND    oc.change_date = (
		SELECT min(change_date)
		FROM   order_change
		WHERE  order_id = oc.order_id
		AND    grief_code = oc.grief_code);

In PL/SQL, you simply construct a cursor order by change_date asc and fetch the first record...

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel