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

Can you have gaps (row expires 5/15, next start 5/17? No coverage on
5/16?) From your example I'd assume not, so:

Select price from file where item = :item and effdate = (select
max(effdate) from file where item = :item and effdate <= :date)

If you can have gaps:

Select price from file where item = :item and effdate = (select
max(effdate) from file where item = :item and effdate <= :date and
expdate >= :date)

Which should result in a null for the subselect which should result in
no price record found.

-Walden


------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----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.