Order by in this situation?

Hi Friends,
I’m stuck up in a strange problem. It would be great if you could help me.
Thanks in advance.

Problem Statement:

Table LEVEL_PRICES
(

  GD_KEY             NUMBER(12) not null,
  DY_BEG_DAY             DATE not null,
  DY_END_DAY             DATE not null,
  FORMULA                VARCHAR2(2000),
  PRICE                  NUMBER(10,5) not null,
  CREATE_USER            VARCHAR2(8) not null,
  CREATE_DATE            DATE not null,
  MODIFY_USER            VARCHAR2(8) not null,
  MODIFY_DATE            DATE not null,

)

Table VOLUMES
(

  GD_GAS_KEY             NUMBER(12) not null,
  VOLUME_SEQ             NUMBER(6) not null,
  DY_BEG_DAY             DATE not null,
  DY_END_DAY             DATE not null,
  DW_BEG_DOW             VARCHAR2(10) not null,
  DW_END_DOW             VARCHAR2(10) not null,
  FORMULA                VARCHAR2(2000),
  PRICE                  NUMBER(10,5) not null,
  CREATE_USER            VARCHAR2(8) not null,
  CREATE_DATE            DATE not null,
  MODIFY_USER            VARCHAR2(8) not null,
  MODIFY_DATE            DATE not null

These are the two details tables of a master table called MyDeal. Every Volume sequence has a price associated with it which comes from the prices table.
The price is defined on a month basis or a set of month basis. E.g. From Jan to April all the volume sequences has the same price. Or Jan-Feb the price is different and for March April the price is different.

Whenever there is a change in the prices table <month or price, formula> a trigger updates the volumes table. Now this works smoothly when there are no date overlaps and there is a single record which needs to be saved.

Having said that I mean to say: If a record exists ‘1-Jan-07’ to 1- Apr-07’ and then I try to add ‘1-jan-07’ to ‘1-Feb-07’, the application throws an exception asking us to correct the overlap. Now the user tends to correct the previous record i.e. ‘1-Jan-07’ till ’30— April07’. The user updates this to cover March through April. Now this is a bulk update <restricting which is beyond scope of the problem >.

For the new record when the trigger gets fired for Volumes, the query below gets the record from this prices table to get the formula depending upon the dates.

SELECT FORMULA
    INTO FRM,

       FROM PRICES
       WHERE GD_KEY = :NEW.GD_KEY
         AND DY_BEG_DAY <= :NEW.DY_END_DAY
         AND DY_END_DAY >= :NEW.DY_BEG_DAY
         AND ROWNUM = 1

As this record tries to pick up only one record and it comes out to be the original record <As the Beg day are same for both the records it picks up the older one> giving us a wrong update of formula on the volumes table.
However I tried ordering it by the beg day which does not help in this scenario. I also tried using modify date to filter this out but it wont make sense in practical scenario when the record updating happen within fraction of seconds. If we consider ordering this by end day even in that case it fails if we give the end day as same.

What could be the possible condition to filter out the exact record?

Regards,
Swapnil Kale. Received on Thu Feb 08 2007 - 14:51:02 CST