Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Order by in this situation?

Re: Order by in this situation?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 8 Feb 2007 16:02:08 -0800
Message-ID: <1170979328.521319.171300@l53g2000cwa.googlegroups.com>


On Feb 8, 3:51 pm, swapnil.k..._at_gmail.com wrote:
> 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.

How did you go about trying to order it? If you used the rownum and order by togather in the same subquery the results would not have been what you wanted.

select formula into frm from (
SELECT FORMULA

           FROM PRICES
       WHERE GD_KEY = :NEW.GD_KEY
         AND DY_BEG_DAY <= :NEW.DY_END_DAY
         AND DY_END_DAY >= :NEW.DY_BEG_DAY
        order by  dy_end_day

)
AND ROWNUM = 1
>From your problem description I am not sure of what the sort criteria
needs to be so might not always want the lower end date and might need both columns in the sort.

HTH -- Mark D Powell -- Received on Thu Feb 08 2007 - 18:02:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US