RE: Finding the right record (based on effective date) using SQL

Not having an expiration date is a little bump in the road; otherwise, it
would be

 

SELECT * FROM {file} WHERE {your_date} BETWEEN {effective} AND {expiration}

FETCH FIRST 1 ROW ONLY

 

How about this?

 

SELECT * FROM nsp002                 --                               

WHERE nsbseg = 'RRBACF001'           --selecting a specific item


and '2004-05-02' >= nsbpeffdt        --effective on/after 02 May 2004


and '2004-05-02' <=                  --check expiration


case 

when nsbpcandt = '0001-01-01'        --no expiration date? 

then '2099-12-31'                    --stuff a future date 

else nsbpcandt                       --otherwise use actual

end                                                                   

ORDER BY nsbpeffdt desc              --reverse order (application
assumption)

FETCH FIRST 1 ROW ONLY               --get one record only

                                       

I suppose a "between" would be a little more elegant, and I don't know if
this would be nasty performance-wise.

 

Regards,

Reeve Fritchman

Ayers Rock Software LLC

4915 S. West Shore Boulevard

Tampa, Florida 33611-3329

(813) 831-8574 (voice)

(813) 832-6391 (fax)

 

> -----Original Message-----

> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-

> bounces@xxxxxxxxxxxx] On Behalf Of ile rpg

> Sent: Sunday, May 02, 2004 1:25 AM

> To: rpg400-l@xxxxxxxxxxxx

> Subject: Finding the right record (based on effective date) using SQL

> 

>    I'm trying to figure out the best way to select a record by effective
date

>    using an SQL SELECT.

> 

>    My table (higher-order keys omitted) looks like this:

>    Effective    Expiration

>    2004-01-01   2004-20-29

>    2004-03-01   2004-04-30

>    2004-05-01   0001-01-01

> 

>    In civilian RPG, I set up a logical file with a descending key over the

>    date; I do a SETLL with the date field (as the low-order key) and a
READE

>    with a partial key (i.e. no date field).  I'm not just testing for the

>    existance of a record; I need it because it has the current price.

> 

>    For the SQL application, I'm using a table where there are no
descending

>    dates.

> 

>    I think I need to say SELECT...WHERE ... AND my-date BETWEEN effective

> AND

>    expiration OR my_date >= effective AND expiration = '0001-01-01' FETCH

>    FIRST ONE ROW.  Now I'm thinking about using a COALESCE to force

>    0001-01-01 to 9999-12-31; that would simplify the logic considerably.

> 

>    My big question: is this a decent way to handle this logic?
Performance

>    is important, because I do this quite a lot, and I've even thought
about

>    forcing an ordered read with a DESC on the date field.  But I'm afraid
I'm

>    still thinking in RPG terms instead of doing something SQL-like.

> 

>    Thanks for your suggestions,

>    IRG

> 

> 

>
----------------------------------------------------------------------

> 

>    Check out the coupons and bargains on MSN Offers!

> _______________________________________________

> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list

> To post a message email: RPG400-L@xxxxxxxxxxxx

> To subscribe, unsubscribe, or change list options,

> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l

> or email: RPG400-L-request@xxxxxxxxxxxx

> Before posting, please take a moment to review the archives

> at http://archive.midrange.com/rpg400-l.