Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Google search


Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
33%
Installation Wizard into new VRC
39%
Manual into existing VRC
6%
Manual into new VRC
22%
Total votes: 49

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 11th March 2003, 16:39
outra9e's Avatar
outra9e outra9e is offline
Senior Member
 
Join Date: Nov 2001
Location: Crawley
Posts: 178
outra9e is on a distinguished road
Baan: Baan IV c4 - DB: Oracle 8.05 / Oracle 8i - OS: HP-UX
Question writing records from one session into a different table

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
__________________
Best Regards

Andy Farr
Tokyo Electron Europe
Baan & Crystal Enterprise Administrator
Reply With Quote
Sponsored Links
  #2  
Old 11th March 2003, 17:10
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 412
evesely is on a distinguished road
Try something like this:
Code:
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.
Reply With Quote
  #3  
Old 11th March 2003, 17:39
outra9e's Avatar
outra9e outra9e is offline
Senior Member
 
Join Date: Nov 2001
Location: Crawley
Posts: 178
outra9e is on a distinguished road
Baan: Baan IV c4 - DB: Oracle 8.05 / Oracle 8i - OS: HP-UX
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?
__________________
Best Regards

Andy Farr
Tokyo Electron Europe
Baan & Crystal Enterprise Administrator
Reply With Quote
  #4  
Old 11th March 2003, 18:48
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 412
evesely is on a distinguished road
If the record doesn't exist in the second table, skip the select and try something like:

Code:
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...
Reply With Quote
  #5  
Old 12th March 2003, 09:46
outra9e's Avatar
outra9e outra9e is offline
Senior Member
 
Join Date: Nov 2001
Location: Crawley
Posts: 178
outra9e is on a distinguished road
Baan: Baan IV c4 - DB: Oracle 8.05 / Oracle 8i - OS: HP-UX
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
__________________
Best Regards

Andy Farr
Tokyo Electron Europe
Baan & Crystal Enterprise Administrator
Reply With Quote
  #6  
Old 12th March 2003, 14:58
evesely's Avatar
evesely evesely is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, Illinois USA
Posts: 412
evesely is on a distinguished road
You can still call it from after.rewrite or before.rewrite. It would look something like this:

Code:
function update.other.table()
{
	db.set.to.default(ttssma931)
	tssma931.xxxx = othertable.yyyy
	tssma931.aaaa = othertable.bbbb
	...
	db.insert(ttssma931, db.retry)
}
Reply With Quote
  #7  
Old 12th March 2003, 15:04
outra9e's Avatar
outra9e outra9e is offline
Senior Member
 
Join Date: Nov 2001
Location: Crawley
Posts: 178
outra9e is on a distinguished road
Baan: Baan IV c4 - DB: Oracle 8.05 / Oracle 8i - OS: HP-UX
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...
__________________
Best Regards

Andy Farr
Tokyo Electron Europe
Baan & Crystal Enterprise Administrator
Reply With Quote
  #8  
Old 12th March 2003, 19:43
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,064
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
You can use a single select to do insert/update if the records are found or not.
Code:
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
}
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy records c_siek Tools Development 9 18th June 2004 15:43
CUMULATING + FILTERING records in DISPLAY SESSION en@frrom Tools Development 21 25th August 2003 16:16
tfacp9450m000 FIBU Brush Up Kai's Forum Users Deutsches Forum 1 16th November 1999 00:00


All times are GMT +2. The time now is 05:57.


©2001-2018 - Baanboard.com - Baanforums.com