Another puzzler for you all...

here is the scenario....

We peform an import of a flat file into an Oracle (Baan) table. Some of the records are missing information, based on this, an Oracle SP takes these records and puts them into a "holding table". This holding table has a Baan maintain session what I want to do, is when the changes have been made to the data, it is written back to a different table.

Is this possible to do within the script on the session, and if so how?

Cheers

Try something like this:

main.table.io:
after.rewrite:
update.other.table()

...

function update.other.table()
{
select xxyyyzzz.*
from xxyyyzzz for update
where ...
selectdo
<set field values>
db.update(txxyyyzzz, db.retry)
endselect
}


In this case, you can utilize the db.retry.point() and commit.transaction() of the standard program and not issue them yourself.

after.rewrite gets called once for each update record in the main table. In this case, I'm assuming that there is a way to select from the secondary table based upon the current record in the session's main table. If not, we'll have to go another route.

Ed in essence that makes sense.

The idea, is that the record will not exist in the table it is going to be written to.

It exists in a table but needs to be modified eg field data is missing. Once filled in, it is written to a different table and the record that originally existed is deleted from the first table.

Make sense?

If the record doesn't exist in the second table, skip the select and try something like:


function update.other.table()
{
db.set.to.default(t<tablename>)
<set field values>
db.insert(t<tablename>, db.retry)
}


If you want to delete the original record (the one in the session's main table) after inserting into the second table, that becomes a bit more complicated. Let's get this piece working first... :)

Ok that makes sense, so for example, it would look something like this ...

function update.other.table()
{
db.set.to.default(ttssma931 )
<tssma931.instr>
db.insert(ttssma930, db.retry)
}


And I would call this function from the main section?

What would I be calling this function from, is there a db.update: that I would use?

Cheers

You can still call it from after.rewrite or before.rewrite. It would look something like this:


function update.other.table()
{
db.set.to.default(ttssma931)
tssma931.xxxx = othertable.yyyy
tssma931.aaaa = othertable.bbbb
...
db.insert(ttssma931, db.retry)
}

Ed

This is great thanks for this...

so to finish this post off...

***************main i/o********************
before.rewrite

update.other.table()

*******************************************

and then the function is where it should be.

Does that all look good ?

Many thanks for all your help these last few days...

You can use a single select to do insert/update if the records are found or not.

function update.other.table()
{
select xxyyyzzz.*
from xxyyyzzz for update
where ...
selectdo
<set field values>
db.update(txxyyyzzz, db.retry)
commit.transaction()
selectempty
<set field values>
db.insert(txxyyyzzz, db.retry, db.skip.dupl)
commit.transaction()
endselect
}