NEED TO NEXT ROW IF DATE > SYSDATE 1

[getjbb]

Hi,

I hope someone can help me:

The data I am working with is as follows:

Pers_ID Rate Amount Effective_Date Expiration_Date
------- ---- ------ -------------- ---------------
1 13.00 100.00 07/22/2003
1 16.00 200.00 09/15/2000 07/21/2003
1 19.00 300.00 08/05/1996 09/14/2000

The select I am currently running is:

SELECT Amount
FROM Ratetable
WHERE Pers_ID = <An Incoming Person ID>
AND Expiration_Date is null;

My problem is that the select statement will
always select the first row because of the where clause.
For example if today's date was 06/22/2003 I would want
the second row, because the effective date of 07/22/2003
is greater than 06/22/2003.
What I want is an effective date that is not greater than
today's date regardless of the expiration date being null.
I was wondering if there is a way of doing this without
using cursors. Thanks.

getjbb

 

[PHV]

What about adding this (or similar) to the where clause ?

AND Effective_Date<=TODAY

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[getjbb]

Thanks for the tip, but the result is two rows. I only
want the second row after the row where expiration_date is
equal to blank. As I mentioned before I want to do this
without using cursors if possible.

getjbb

 

[PHV]

Can you please expose ALL the criterias to select the correct row ?

 

[rrrkrishnan]

Try this.

Where eff_dt <= todays_dt and
coalesce(expir_dt,date'9999-12-31') > todays_dt

 

[getjbb]

Thanks, but I still received two rows.

 

[rrrkrishnan]

I don't understand how this can return two rows. say todays_dt is 06/22/2003.

Pers_ID Rate Amount Effective_Date Expiration_Date
------- ---- ------ -------------- ---------------
1 13.00 100.00 07/22/2003
1 16.00 200.00 09/15/2000 07/21/2003
1 19.00 300.00 08/05/1996 09/14/2000

For this set of records and this sql
SELECT Amount
FROM Ratetable
WHERE Pers_ID = 1
AND effective_date <= 06/22/2003 and
coalesce(Expiration_Date , date'9999-12-31') > 06/22/2003

You will get only the second record for the first has eff_dt > 06/22/2003 and the third has expir_dt < 06/22/2003.

 

[getjbb]

I am using an Oracle database, which does not have the
command, coalesce. I was informed that nvl was the equivalent of the coalesce command. Maybe coalesce and nvl does not perform the same. I will try your solution
above again.

getjbb

 

[getjbb]

I was informed wrong. I found out that Oracle does have
the coalesce command. I will try the select with coalesce instead of nvl.

getjbb

 

[rrrkrishnan]

nvl is equivalent to coalesce.

try
SELECT Amount
FROM Ratetable
WHERE Pers_ID = 1
AND effective_date <= 06/22/2003 and
nvl(Expiration_Date , to_date('9999-12-31','yyyy-mm-dd')) > 06/22/2003

 

[getjbb]

rrrkrishnan,

Thanks a lot. It worked.

getjbb