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

["ile rpg" <ile_rpg_guy@xxxxxxxxxxx>]

   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!