I have a session which maintains detail records, each with a line number (part of index1). I need to add the ability to allow the user to renumber the lines by a given interval. This would be similar to the "Renumber - interval 10" function under Specific on session ttadv3561m000 Menu Fields.

Is there an easy way to do this? Is there a dll available, and if so, what is it and what are the input parameters?

Thanks in advance for your help!

We had a similar request to do that with routers. After looking at it a few different ways, I decided to create an Oracle procedure that was callable from a Baan session I created. In it I pass the primary key values to the procedure through the session. The Baan domain restricts the value on the sequence number to three digits. In the Oracle procedure, I don't have that restriction since I'm going directly against the table. The procedure uses a two pass replacement. On the first pass I increment the sequence number to a value larger that three digits. This prevents me from coming across an identical value and getting a key violation. On the second pass I increment the sequence to a range that fits the three digit size (i.e. 10, 20, 30, ...). Then I commit the updates and exit back to the Baan session. The Oracle procedure is called using the "run.prog" function in Baan tools. It in turn calls out to a shell script that invokes a sqlplus script that passes the parms from the Baan session to the Oracle procedure.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hello gguymer,

I am the DBA at my company and I had already thought of using an Oracle procedure. I find myself using Oracle procedures lauched from Baan sessions quite frequently to perform programming tasks which I can not find a way to do in Baan programming.

If no one else can offer a Baan programming solution, I will follow your advice and write an Oracle procedure.

Thanks for your reply!

Hi,

following should do the trick:


function renumber()
{
long new.pono
long max.pono

select max(pono): max.pono
from tablexyz
selectdo
endselect

db.retry.point()

new.pono = max.pono +10

select *
from tablexyz for update
order by pono
selectdo
db.delete(ttablexyz, db.retry)
tablexyz.pono = new.pono
db.insert(ttablexyz, db.retry)
new.pono = new.pono +1
endselect

new.pono = 10

select *
from tablexyz for update
order by pono
selectdo
db.delete(ttablexyz, db.retry)
tablexyz.pono = new.pono
db.insert(ttablexyz, db.retry)
new.pono = new.pono + 10
endselect

commit.transaction()
}

I not aware about Baan V . But in Baan IV there is pre-defined variable modify.prim.key . If u set this to true , it will allow u to change the key values in session.

Vahdani, Just curious it should be possible to do db.update in Baan V instead of db.delete and db.insert when changing the primary key values. We can do this in Baan IV.

Hallo Hitesh,

you're right with "db.update". I was so engrossed in the logic of the action that I did not see the obvious. Now where is that famous humble pie?

Thanks to all who helped!

I was able to make the Baan code work wth 2 changes.

I replaced ...

select max(pono): max.pono

with ...

select count(pono): max.pono

and ...

new.pono = max.pono +10

with ...

new.pono = 9998 - max.pono

These changes were necessary because if a subsequent renumber function is performed, the original code can give duplicate records during the update process.

Thanks to all who helped!

I was able to make the Baan code work wth 2 changes.

I replaced ...

select max(pono): max.pono

with ...

select count(pono): max.pono

and ...

new.pono = max.pono +10

with ...

new.pono = 9998 - max.pono

These changes were necessary because if a subsequent renumber function is performed, the original code can give duplicate records during the update process.

function renumber(long step)
{
long new.pono

db.retry.point()

new.pono = 0

select *
from tablexyz for update
order by pono
selectdo
new.pono = new.pono + 1
tablexyz.pono = new.pono
db.update(ttablexyz, db.retry)
endselect

if step > 1
then
new.pono = new.pono * step

select *
from tablexyz for update
order by pono desc
selectdo
tablexyz.pono = new.pono
db.update(ttablexyz, db.retry)
new.pono = new.pono - step
endselect
endif

commit.transaction()
}


Example: You have records with pono 1,2,3,7,11,163,800,999. The function will change the pono at first to 1,2,3,4,5,6,7,8 and then with e.g. step 10 to 10,20,30,40,50,60,70,80 (the function is processing the records in descending order but starting with the highest pono).

Clever stuff, and.....