Re: pl/sql question

The correct statement is

SELECT X_STATUS,STATUS_DATE
 INTO V_STATUS, V_DATE
 FROM x x1
WHERE PK_GUID = 'guid1'
AND
status_date =
(select max(status_date)
 from x x2
 where x2.pk_guid = x1.pk_guid
 and x2.x_status = x1.x_status
 and status_date <= v_input_date
);

Hth,

Sybrand Bakker, Oracle DBA

<ringosoft_at_my-deja.com> wrote in message news:94krsc$hs2$1_at_nnrp1.deja.com...
> I'm stumped. Given the following table x (with Primary key pk_GUID,
> x_status):
> Fieldname: pk_GUID x_status status_date
> guid1 1 20-dec-2000
> guid1 2 20-jan-2001
> guid1 3 20-mar-2001
> ...
>
> I want to get the x_status with the highest date (less than an input
> date) for pk_guid=guid1 into a variable V_STATUS using a single pl/sql
> statement (here, it would give me '3').
>
> I thought I could use the following to get a single record, but it
> returns multiple rows, and so I get a 'TOO_MANY_ROWS' exception when
> v_input_date is greater than 20-jan-2001:
>
> SELECT X_STATUS, MAX(STATUS_DATE) INTO V_STATUS, V_DATE
> FROM x WHERE PK_GUID = 'guid1' AND
> STATUS_DATE <= v_input_date GROUP BY X_STATUS;
>
> I tried to use this approach because I know you can't use aggregate
> functions in a where clause. Otherwise this would be much simpler. As a
> side note, can anyone think of a reason why Oracle won't allow that?
>
> Can I use a single pl/sql query statement to get the data I need?
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 23 2001 - 16:03:41 CST