Hi all

Help me please. I want to use the data tdinv760 to update trdrp001 (sales forecast). I use the script below, but I get an error 100 (Duplicate value) on trdrp001. How do I fix this, what am I doing wrong.

function update.drp001()
{

long i

db.retry.point()

i = 0

tdinv760.cwar = ""
tdinv760.item = ""
tdinv760.cntr = ""
tdinv760.year = 0

select tdinv760.*, trdrp001.*
from tdinv760, trdrp001 for update
where tdinv760._index1 inrange {"SLS", :item.f, :cntr.f, :year.no}
and {"SLS", :item.t, :cntr.t, :year.no}
order by tdinv760._index1 with retry repeat last row
selectdo
for i = pern.f to pern.t
trdrp001.cwar = tdinv760.cwar
trdrp001.item = tdinv760.item
trdrp001.cntr = tdinv760.cntr
trdrp001.yrno = year.no
trdrp001.peri = i
trdrp001.pqua = tdinv760.fupp(i)
db.update(ttrdrp001, db.retry)
endfor
commit.transaction()
selectempty
for i = pern.f to pern.t
trdrp001.cwar = tdinv760.cwar
trdrp001.item = tdinv760.item
trdrp001.cntr = tdinv760.cntr
trdrp001.yrno = year.no
trdrp001.peri = i
trdrp001.pqua = tdinv760.fupp(i)
db.insert(ttrdrp001)
endfor
selecteos
commit.transaction()
endselect
commit.transaction()
}

Any help or suggestions will be appreciated.

Thanks

what will be the content of the inv760-record in the selectempty ??????????
right....EMPTY......over and over again........

I think the db.insert should have a db.retry as second argument.....
And when you enter the selectempty the value of tdinv760 is unknown...

Kind regards,
Dick

You should split up your query:

First select the records of table tdinv760.
Within the selectdo for each period select trdrp001. If in selectdo, update fields and do a db.update. In selectempty fill all fields and do a db.insert. So you will get something like:

db.retry.point()

select tdinv760.*
from tdinv760
where tdinv760._index1 inrange {"SLS", :item.f, :cntr.f, :year.no}
and {"SLS", :item.t, :cntr.t, :year.no}
selectdo
for i = pern.f to pern.t
select trdrp001.*
from trdrp001 for update
where trdrp001._index1 = {:tdinv760.cwar, :tdinv760.item,
:tdinv760.cntr, :tdinv760.year}
selectdo
trdrp001.pqua = tdinv760.fupp(i)
db.update(ttrdrp001, db.retry)
selectempty
trdrp001.cwar = tdinv760.cwar
trdrp001.item = tdinv760.item
trdrp001.cntr = tdinv760.cntr
trdrp001.yrno = year.no
trdrp001.peri = i
trdrp001.pqua = tdinv760.fupp(i)
db.insert(ttrdrp001, db.retry)
endselect
endfor
commit.transaction()
selecteos
commit.transaction()
endselect

Success

Youp

Wow, what a good analysis.
Now I understand what is meant with the script.
I made a little change (in red):

db.retry.point()

select tdinv760.*
from tdinv760
where tdinv760._index1 inrange {"SLS", :item.f, :cntr.f, :year.no}
and {"SLS", :item.t, :cntr.t, :year.no}
selectdo
for i = pern.f to pern.t
select trdrp001.*
from trdrp001 for update
where trdrp001._index1 = {:tdinv760.cwar, :tdinv760.item,
:tdinv760.cntr, :tdinv760.year,
:i}
as set with 1 rows
selectdo
trdrp001.pqua = tdinv760.fupp(i)
db.update(ttrdrp001, db.retry)
selectempty
trdrp001.cwar = tdinv760.cwar
trdrp001.item = tdinv760.item
trdrp001.cntr = tdinv760.cntr
trdrp001.yrno = year.no
trdrp001.peri = i
trdrp001.pqua = tdinv760.fupp(i)
db.insert(ttrdrp001, db.retry)
endselect
endfor
commit.transaction()
selecteos
commit.transaction()
endselect

Thanks everybody, this works like a charm!!!!

Have a nice day!!