Re: Six of one . . .

They represent 'events' in a shipping system. Kind of hard to process the shipment of an item when it hasn't yet been "received."

On Tue, 17 Sep 2002 15:34:37 -0400, "Alan" <alan_remove_this_shein_at_erols.com> wrote:

>Why do the rows need to be processed in a particular order?
>
>
>"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message
>news:3d877d53.112737467_at_ausnews.austin.ibm.com...
>>
>>
>> Platform: Oracle 8.1.7 on NT4
>>
>> Developer is involved in a project of converting an application (written
>in
>> Microfocus Cobol) from DB2/2 on OS/2 to Oracle on NT. The general problem
>at
>> hand is to identify a selection of rows, sorted ascending by a 'timestamp'
>field
>> (implemented in our 8.1.7 db as a char(26)), processing each row in turn.
>> Processing of each row includes modifying a 'status' column that removes
>it from
>> the original selection criteria.
>>
>> In the original application, it is implemented as shown with this pseudo
>code:
>>
>>
>> ---- begin pseudo code example -----
>>
>> Begin loop
>>
>> SELECT MIN(CTE_DTS)
>> INTO :ws-dts
>> FROM MY_TABLE
>> WHERE APPC_STA = 'P'
>> AND CTE_DTS > '0001-01-01-00.00.00.000000'
>>
>> --- do a bunch of processing based on the row returned above, then change
>the
>> status of that row ---
>>
>> UPDATE my_table
>> SET appc_sta = 'Z'
>> WHERE APPC_STA = 'P'
>> AND CTE_DTS = :ws-dts
>>
>> commit
>> End loop
>> ---- end pseudo code example -----
>>
>>
>> She was having some difficulty with inconsistent results, which I chalked
>up to
>> inconsistent test cases. (She's a fairly inexperienced programmer, and
>this is
>> her first exposure to Oracle). By the time I was able to take a look at
>it,
>> she had replaced the above with a cursor, thusly;
>>
>> ---- begin pseudo code example -----
>>
>> Declare cursor c1 as
>> SELECT CTE_DTS
>> FROM MY_TABLE
>> WHERE APPC_STA = 'P'
>> AND CTE_DTS > '0001-01-01-00.00.00.000000'
>> ORDER BY CTE_DTS
>>
>> Loop until no-more-rows
>> Fetch cursor c1 into . . . .
>> Do a bunch of processing
>> Update row returned by the fetch
>> commit
>> end loop
>> ---- end pseudo code example -----
>>
>> So now I'm wondering how to adviser her on the best approach from an
>Oracle
>> standpoint. Which general approach would be preferred? My gut feel is
>the
>> cursor loop.
>>
>> Begin-loop
>> SELECT MIN(timestamp-field)
>> WHERE condition-1
>>
>> UPDATE so that condition-1 is no longer true
>> COMMIT
>> End-loop
>>
>> OR . . .
>>
>> DECLARE CURSOR
>> WHERE condition-1
>> ORDER BY timestamp-field
>>
>> Begin-loop
>> FETCH CURSOR
>> UPDATE so that condition-1 is no longer true
>> COMMIT
>> End-loop
>>
>>
>> I'm shooting from the hip here, so I may have missed some detail, but if
>you
>> remember that I'm only presenting pseudo code, the general problem and
>question
>> should be apparent.
>>
>> --
>> Ed Stevens
>> (Opinions expressed do not necessarily represent those of my employer.)
>
>

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)