Background:

In our system, we have quite a few items that have become obsolete and have been replaced be newer items. These newer items do not have 'Issue History' (tdinv750 & tdinv760) and we would like to use the history of obsolete items so that we can forecast our newer items better. We have decided to create a session that will copy the history from a source (Obsolete) Item to a Target (Newer) Item.

Problem:

I understand that it is not possible to have two active references to same table more than once. Hence I am wondering how can I take the recordset of source item, compare it with the recordset of target item and then either update or insert the issue history in a period, one period at a time.

The way I have done right now is to open the recordset of target item (for example from tdinv750) and stored the values in to some variables. Then I have reset the field values of source item (tdinv750.xxxx) and opened the recorset of target item from same table for update. I had to reset the field values of source item to empty/zero as otherwise as soon as I will open the recorset of target item, the values of target item will get automatically set from source item recordset. So after opening the recordset for update, I will compare the values in the target item from those variables and update the issue history based on some logic.

My question is, is there a way I can do the above without having to define variables. Can I define two different pointers to same table and if yes, how can I use them in query? How would I refer to the table fields of these two different pointers, if at all possible?

Your help will be much appreciated.

I don't have the exact key details for your tables, but you can proceed with code similiar to the following:


db.retry.point()
select tdinv750.*
from tdinv750
where tdinv750_index? = {:old_item}
selectdo
|* change key fields here to renamed item
|* be careful if seq field exists

tdinv750.item_field = new_item
db.insert(tdinv750,db.retry)
endselect
commit.transaction()

Well, that is what I am doing but I wanted to find another way to do that. This is how my code looks like:

function read.issue.history.from.tdinv750()
{
select tdinv750.*
from tdinv750
where tdinv750._index1 = {:itmf}
order by tdinv750._index1
selectempty
|No Item History to be copied
selectdo
cntr.f = tdinv750.cntr
year.f = tdinv750.year
aupp.f = tdinv750.aupp
cwar.f = ""
clear.rcd.tdinv750() |This function reset table field values
copy.issue.history.from.tdinv750()
endselect
}

Now aupp.f (tdinv750.aupp) has depth of 13, I want to loop through each aupp.f(i) value so that I can compare the value of two similar periods from source and target items before updating them. After comparing it in another function, I store the actual value in a variable called qty.to.copy and then use that in the function below to update the target item issue history.

function copy.issue.history.from.tdinv750()
{
select tdinv750.*
from tdinv750 for update
where tdinv750._index1 = {:itmt, :cntr.f, :year.f}
order by tdinv750._index1
selectempty
tdinv750.item = itmt
tdinv750.cntr = cntr.f
tdinv750.year = year.f
tdinv750.aupp(i) = qty.to.copy * found.conv
selectdo
tdinv750.aupp(i) = tdinv750.aupp(i) + (qty.to.copy * found.conv)
endselect
}

So you can see I had to first store the value from source item into intermediae variables such as cntr.f, year.f, aupp.f and then use them for updating the target item. The session is working just fine but it was ok in this case as I had only 4 fields. In case there are 10/20 fields in a table and I would like to do the same, how can I do something similar without declaring 10/20 (or more) intermediate variables.

Where is your db.insert() and db.update() calls?


function copy.issue.history.from.tdinv750()
{
select tdinv750.*
from tdinv750 for update
where tdinv750._index1 = {:itmt, :cntr.f, :year.f}
order by tdinv750._index1
selectempty
tdinv750.item = itmt
tdinv750.cntr = cntr.f
tdinv750.year = year.f
tdinv750.aupp(i) = qty.to.copy * found.conv
db.insert(tdinv750,db.retry)
selectdo
tdinv750.aupp(i) = tdinv750.aupp(i) + (qty.to.copy * found.conv)
db.update(tdinv750,db.retry)
endselect
}

Those statments are their in my actual code as I didn't copy the exact code I had some other statements too that were taking care of 'Test Run' option, if user selects to run that first.

With
db.row.length(ttdsls040, row.length)
alloc.mem(buffer.sls040, row.length)
buffer.sls040 = rcd.ttdsls040

you can save a whole record in one variable. You ca not edit this variable, but you can restore the record later with

rcd.ttdsls040 = buffer.sls040

or also

rcd.ttdslsXXX = buffer.sls040

if the structure of both tables is the same.

All values of your tablefields will be overwritten by the values in your variable.

I think it's the easiest way to transfer a record or save all fields as a backup.

Maybe this helps
csecgn

Thanks for the response but I had tried this but as you mentioned, coud not manipulate the field values, which in my case I want to do.

Thanks anyays.

It's right, you cannot manipulate the field value directly. But you can do it indirectly. If I understood your problem correctly, you want to save 10/20 vars and manipulate one or two. With this way you can save all vars, and if you need the record, restore it. After this update the fields you want to manipulate and write db.update() or db.insert(). So you have one container for all and just need vars for the fields you want to update.
And, if you restore the old values after your actions you will not loose your recordpointer within an selectdo.

csecgn