• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Table IO Update statement in Xe

Xe_na_thanks

Active Member
Dear list,

Config: Xe ,SP15 , AS400, DB2/400.

Problem.

I want to simulate the following SQL statement using Table IO in a Named Event Rule;

UPDATE PYDTA/F5500100 SET SCUB02 = 100 WHERE SCMCU = ' 194'
and SCITM = 100321 and sctrdj <= 101200

I am reading F5500100 in a select, fetch next loop and then updating it (IN THE LOOP) with an update using handles.

e.g.

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

F5500100(VA evt_idF5500100Handle_F55100).Select
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
//
While SV File_IO_Status is equal to CO SUCCESS
//
// Some processing
//
F5500100(VA evt_idF5500100Handle2_F5510).Update
VA evt_szCostCenter_MCU = TK Business Unit
VA evt_mnIdentifierShortItem_ITM = TK Item Number - Short
VA evt_jdStoredTransDate_TRDJ = TK Date - Order/Transaction
VA evt_Quantity_UB02 -> TK Quantity UB01

//
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
//
End While

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


But What I want is;


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

F5500100(VA evt_idF5500100Handle_F55100).Select
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
//
While SV File_IO_Status is equal to CO SUCCESS
//
// Some processing
//
F5500100(VA evt_idF5500100Handle2_F5510).Update
VA evt_szCostCenter_MCU = TK Business Unit
VA evt_mnIdentifierShortItem_ITM = TK Item Number - Short
VA evt_jdStoredTransDate_TRDJ =< TK Date - Order/Transaction
**
VA evt_Quantity_UB02 -> TK Quantity UB01

//
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
//
End While

*********************************************************************
As you can see I want to use a less than or equal to conditioner for the transaction date. I can't seem to achieve this using straight forward table IO as when I click on the operation field to change this to a less than or equal to sign nothing happens (it does if you are in select of course) I wondered if any of you clever people out there had any thoughts on this. I can think of one way to do it which would be to select within a select using handles but that would be incredibly slow!

Any help would be greatly appreciated.

Regards,

Adrian.
 

barrier

Reputable Poster
1. Set up variable containing your select criteria

2. Use File I/O select with the variables, adjusting the relationship
criteria to match you requirements

3. In side a while (something is
 

Zoltan_Gyimesi

Legendary Poster
Hi Adrian,

1.) If I am right, when you use handle then you have to use OpenHandle, CloseHandle too. I suppose you did.
2.) Have you pick up the values of the index fields for the select part of the Update in the FetchNext statements?
3.) Do you want to place the same value for UB01 in all records?
If yes, then maybe you can use a single partial key update statement instead of the WHILE loop (I have never done yet but done for Delete) but it could depend on your index(es). What is your index on the Select/Fetch/Update statements?
Please, add some more information to us to help you more. Thanks.

Read You Again,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Zoltan_Gyimesi

Legendary Poster
Adrian,
I just checked the Development Tools documentation (although for an other reason but just Table I/Os).
There is a "Valid Mapping Operators" subsection which tells that "Equal to" is allowed only for the index fields in the Update statement. But we can go on to try because as we know, the documentation is not always the law.
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Discover how to build no-code data integrations and business process automations.

vbojan

Well Known Member
Hi Adrian,

You need to Select, FetchNext and save values for ALL fields belonging to primary key and Update that record.

E.g.

F5500100(VA evt_idF5500100Handle_F55100).Select
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
VA evt_szCostCenter_MCU <- TK Business Unit
VA evt_mnIdentifierShortItem_ITM <- TK Item Number - Short
VA evt_jdStoredTransDate_TRDJ <- TK Date - Order/Transaction

//
While SV File_IO_Status is equal to CO SUCCESS
//
// Some processing
//
F5500100(VA evt_idF5500100Handle2_F5510).Update
VA evt_szCostCenter_MCU = TK Business Unit
VA evt_mnIdentifierShortItem_ITM = TK Item Number - Short
VA evt_jdStoredTransDate_TRDJ = TK Date - Order/Transaction
VA evt_Quantity_UB02 -> TK Quantity UB01

//
F5500100(VA evt_idF5500100Handle_F55100).Fetch Next
VA evt_szCostCenter_MCU <- TK Business Unit
VA evt_mnIdentifierShortItem_ITM <- TK Item Number - Short
VA evt_jdStoredTransDate_TRDJ <- TK Date - Order/Transaction
//
End While


Hope this helps,

Bojan.
 

Larry_Jones

Legendary Poster
Adrian,

I'm missing something here. Why don't you put your date filter (sctrdj <= #date value#) in the select statement? That's what its for. Once a cursor has been established with a select statement there is no need or purpose in referencing key fields in the fetch next statements.

Or are you trying to update multiple records with a single Update statement?

Confused in Spokane :(

Larry Jones
ljones@wagstaff.com
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 

Xe_na_thanks

Active Member
Dear All,

Thank you for your replies,

To answer some of them.

The select/fetch next loop as I said in my original posting is a solution but not a feasible one as it is too slow.

Zoltan,

1, Yes I did use open and close but I considered it not pertinent to the example.

2, Yes. I did pick up the index keys from the fetch next statement.

3, No I don’t want to update all.

4, Documentation: The devil and all his little minions wrote those volumes so don't trust them and to paraphrase whilst reading if you see a bright light and hear the voices of your ancestors calling then close the book.

Bojan,

I'm sorry it doesn't too slow, but thanks anyway.

Confused of Spokane (Planet?)

Yes I am trying to update multiple records with one update statement.

I have three solutions possibly maybe some opinions would help.

1, Create file with MCU,ITM,TRDJ. Have AS400 trigger perform an SQL statement after insert such as ;

UPDATE PYDTA/F5500100 SET SCUB02 = 100 WHERE SCMCU = ' 194'
and SCITM = 100321 and sctrdj <= 101200

and then delete the record in the new file? The library could either just be the same as the files library or I could go to the data source master and find the library from there.

2, Create the above file and join in a BSVW with a Less than or equal to join for the transaction date.

Write a record to the second file and then update the business view.

In theory this should update all those records that are equal to ITM and MCU and Less than or Equal to Transaction Date (TRDJ)

3, C BSFN!

What do you think?
 

Zoltan_Gyimesi

Legendary Poster
Hi Adrian,

Do you have an index on your table which begins with MCU, ITM, TRDJ fields in this order (the first two could be in the reverse order)? If yes then have you tried a single update statement

F5500100.Update
194 = SCMCU
100321 = SCITM
101200 <= SC101200
100 -> SCUB02

selecting and using this index in the update statement? If yes then what was your result?

On the other hand I am very curious to that does your 3rd scenario work or not.

Regards,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Larry_Jones

Legendary Poster
Adrian,

I like your idea #1 of using a trigger on a custom table that you pass values and params to using the data fields from the custom table insert. This seems to me a flexible approach easily adapted/expanded to other mass update type operations.

Question: How many rows (max) can be updated by a single operation? (how big is your problem?)


Larry Jones
ljones@wagstaff.com
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 
Maybe this will help you, create a conditional group section. Define a business view for it. Set up your data selection either on the section or in the initialize event. So this would fetch all the rows that match your criteria. In the DO event which would fire for each row returned you could do your update or whatever other processing was needed. In fact you could use any of the events to do processing before or after a row was fetched. I haven't tested doing updates with this but, I've used it for doing special totaling and it seems pretty fast.


XE SP14.1, Oracle 8.1.6 on W2K,Citrix/W2K, NT 4.0
 

Xe_na_thanks

Active Member
Zoltan,

If I could do that I wouldn't have a problem, but that is my problem entirley , you can't do that!

Regards,

Adrian.
 

Xe_na_thanks

Active Member
Mike,

This is still only reading all records and updating them one at a time with the overhead of starting a UBE (i'm writing a business function). I can simulate this by a select , fetch next loop; it becomes too slow. Thank you for your suggestion though. I'm going for the trigger option.
 
Top