pl/sql question

From: <ringosoft_at_my-deja.com>
Date: Tue, 23 Jan 2001 21:08:38 GMT
Message-ID: <94krsc$hs2$1@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 - 15:08:38 CST