Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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