Trying to load my table with data from another table, in another company specified on my form. I have it inserting the first line, but thats it. Each line created is suppose to increment by one and then insert the data.

This is what I have

function void sel_max_numb()
{
select max(wtsls100.numb):max_numb
from wtsls100
where wtsls100.matl= :wtsls100.matl
and wtsls100.item = :wtsls100.item
selectdo
endselect

for i =1 to filled.occ
restore.rcd.main(i)
if wtsls100.numb > max_numb
then
max_numb = wtsls100.numb
endif
endfor

}
function get.bom.lines()
{
long count
db.retry.point()
select wtsls100.*, tibom010.*
from wtsls100 for update, tibom010
where tibom010.mitm = :wtsls100.item
and tibom010._compnr = :wtsls100.bomc
and wtsls100.item = :wtsls100.item
and wtsls100.cuno = :wtsls100.cuno
and wtsls100.pono = :wtsls100.pono
and wtsls100.cont = :wtsls100.cont
order by wtsls100._index1
selectdo
sel_max_numb()
wtsls100.numb = max_numb
wtsls100.matl = tibom010.sitm
db.insert(twtsls100,db.retry)
commit.transaction()
endselect
}

I am confused - are you inserting or updating? Or maybe there is more code that you did not post. The reason I ask is that on the insert I see where you are only setting two fields for the wtsls100 table - yet above in the query I see other fields for wtsls100. So should that be db.insert or should it be db.update?

Mark

The session is a multi occ form.
The following exist before I choose the update
wtsls100.cuno, cdel, item, cont, cdel, bomc.

When i use db.update, i get error 206 record is not locked, if a record exists, but if no records exist, nothing gets inserted.

Not sure why you get the record is not locked, but typically what we do for these type serial numbers is something like below:

function read.serial.number()
{
select max(tppro603_a.seqn):serial.number
from tppro603 tppro603_a
where tppro603_a._index1 = {:tppro603.cprj, :tppro603.cspa, :tppro603.nprj}
selectdo
selectempty
serial.number = 0
endselect
return(serial.number)
}
function search.last.used.seqn.on.screen()
{
long occ.no

for occ.no = 1 to (filled.occ - 1)
do.occ(occ.no,check.last.used.sern)
endfor
}

function check.last.used.sern()
{
if tppro603.seqn > serial.number then
serial.number = tppro603.seqn
endif
}


Then during the insert of a record we do something like this:

serial.number = read.serial.number()
search.last.used.seqn.on.screen()
tppro603.seqn = serial.number + 1

This way as you insert records on the screen you get a new serial number. In this example at the start of each new record in the form I ran these three lines of code.

As for your query on wtsls100

function get.bom.lines()
{
long count
db.retry.point()
select wtsls100.*, tibom010.*
from wtsls100 for update, tibom010
where tibom010.mitm = :wtsls100.item
and tibom010._compnr = :wtsls100.bomc
and wtsls100.item = :wtsls100.item
and wtsls100.cuno = :wtsls100.cuno
and wtsls100.pono = :wtsls100.pono
and wtsls100.cont = :wtsls100.cont
order by wtsls100._index1
selectdo
sel_max_numb()
wtsls100.numb = max_numb + 1
wtsls100.matl = tibom010.sitm
db.update(twtsls100,db.retry)
commit.transaction()
selectempty
wtsls100.item = ....
wtsls100.numb = max_numb
wtsls100.matl = tibom010.sitm
db.insert(twtsls100,db.retry)
endselect
}


Is there more code that may show exactly what you are trying? Are you updating one record at a time on wtsls100? Or are you running a routine that pulls all records from wtsls100 and updates them all at once?

Mark

This is what I am doing:
I have a maintenance session open, that has my header information inserted
(cuno, cdel, cont, pono, item, bomc).
The details in this session need to be filled with the bom lines, and each line inserted is incremented by one.(when selection load bom lines)
I have attached a screen shot of my session, maybe it will make more sence.

I tried your example, but still no luck.

I noticed that you joined wtsls100 and tibom010 which means you already expect all of the records to be in wtsls100. If not found then you want to add them (at least that is what I think). I just basically separated the queries and modeled it after my outbound session which takes an order and loads the session table.

Mark


function void sel_max_numb()
{
select max(wtsls100.numb):max_numb
from wtsls100
where wtsls100.matl= :wtsls100.matl
and wtsls100.item = :wtsls100.item
selectdo
endselect

for i =1 to filled.occ
restore.rcd.main(i)
if wtsls100.numb > max_numb then
max_numb = wtsls100.numb
endif
endfor

}
function get.bom.lines()
{
long count
db.retry.point()
| Find bom items
select tibom010.*
from tibom010
where tibom010.mitm = :wtsls100.item
and tibom010._compnr = :wtsls100.bomc
selectdo
select wtsls100.*
from wtsls100 for update
where wtsls100.item = :tibom010.mitm
and wtsls100.matl = :tibom010.sitm
......
as set by 1 rows
selectdo
.....update only the wtsls100 fields that need to be updated...
db.update(twtsls100,db.retry)
selectempty | Record not found
sel_max_numb()
wtsls100.numb = max_numb + 1
wtsls100.matl = tibom010.sitm
....all other wtsls100 fields get set.........
db.insert(twtsls100,db.retry)
endselect
commit.transaction()
endselect
}

It loads the lines most of the time, not sure why it hangs sometimes.
It doesn't seem to work for the update. It only loads a few parts than hangs.

I would recommend, debug the code instead of compile.

Regards,

-Suhas

Yes - run it in debug mode and check the fields that are being updated. Make sure during the update none of the primary index fields are being updated - this would cause a duplicate key error. You might want to also post the new version of the code again.

Mark

It seems to be fine now. Thanks for all your help