RE: Update a table from EXTNAME-defined data structure using search argument

I'm not a huge fan of converting "all" access to sql views.
It overly complicates some of the coding, and doesn't really help anything when you have to account for historic coding practices anyway.

We have a change management system that takes care of recompiling dependent objects.

So what does "UPDATE SET ROW" really give you?
It allows you to have one less part of the program you have to deal with when updating a database.

Yes, you have to add logic to validate and load your new fields.
But at least you don't have to remember to add one more column to that one update statement.


Reading from one view to load another table is real easy when the fields are named the same.
All you would need is two qualified data structures and EVAL-CORR.


Dcl-ds ds1 extname('TABLEFOROUTPUT') QUALIFIED;
Dcl-ds ds2 extname('TABLEFORINPUT) QUALIFIED;

// Clear DS1 to set default values for fields based on data type.
Clear DS1;
// map corresponding fields from the input table/view format, to the output format.
EVAL-CORR DS1 = DS2;
// set values for any DS1 unique fields.

// update the record.
EXEC SQL UPDATE TABLEFOROUTPUT
OVERRIDING SYSTEM VALUE
SET ROW = :DS1
WHERE KEY = :DS1.KEY;





Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Cassidy
Sent: Tuesday, July 19, 2022 1:31 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Update a table from EXTNAME-defined data structure using search argument

I learned something new today with that SET ROW! Thanks Chris! I ended up running the UPDATE with the SET listing each column separately. Safer that way. Although I did create an SQL View object creat
External (cfuture@xxxxxxxxxxx<mailto:cfuture@xxxxxxxxxxx>)

Report This Email<https://protection.inkyphishfence.com/report?id=d2luY29mb29kcy9jaHJpcy5oaWViZXJ0QHdpbmNvZm9vZHMuY29tLzUzMDRjZmQ5MDFiYWU5ZDdjZmFiZjIwYzcwMDM3MzYyLzE2NTgyNTkxMDMuNTk=#key=2116209a6958951359b6063ef5f83cab>


I learned something new today with that SET ROW! Thanks Chris!



I ended up running the UPDATE with the SET listing each column

separately. Safer that way.



Although I did create an SQL View object created for another program

where I fetch into a data structure that is defined with _EXTNAME

(viewname)_. Actually I'm using columns from that View to update one of

the physical files. So I think using the data structure may have given

me trouble..



UPDATE filename SET col1 = :value1, col2 = :value2, .... WHERE key1 =

:kval1 and key2 = :kval2.



--Alan