RE: Populating fields based on field name in a file

You need to use PREPARE to do a dynamic SQL or EXECUTE IMMEDIATE and build
the string like this:

Eval mySQL = 'UPDATE ' + R1EFIL + ' SET ' + R1EFLD + '=' + R1EFVL + ' WHERE
INUMBR = ' + M1SSKU

 /EXEC SQL
C+ EXECUTE IMMEDIATE :mySql
 /END-Exec

Or something like that.

-Bob Cozzi
www.RPGxTools.com
RPG xTools - Enjoy programming again.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Mark Allen
Sent: Tuesday, October 25, 2005 12:59 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Populating fields based on field name in a file

0001.11 c/Exec Sql                
0001.12 c+ Update :R1EFIL         
0001.13 c+  set :R1EFLD = :R1EFVL 
0001.14 c+  where INUMBR=:M1SSKU
0001.15 c/End-Exec                
 
This is what I would like where :R1EFIL contains the value 'INVMST' ,
:R1EFLD='BYRNUM' and :R1EFVL = 21, INUMBR  is field in :R1EFIL (INVMST) and
:M1SSKU = (example) 888
 
That is I would update record with SKU#=888 in file INVMST setting the
BYRNUM field to 21
 
Keying in program gives me invalid token at the : in R1EFIL and R1EFLD, am I
missing something (besdies my sanity that is?)

Bob Cozzi <cozzi@xxxxxxxxx> wrote:
You have two choices, the grunt way, as you've described it already in your
original note, and to use SQL and build the select statement, something
like:
SELECT EFV FROM INVMST into :BUYR

I recently worked on trying to create the following:

Eval BUYR = GetFieldValue('EFV')

However, programmically it isn't known how to do that with RPG IV and the
ILE environment, aside from using SQL or some other dynamic query tool.

-Bob Cozzi
www.RPGxTools.com
RPG xTools - Enjoy programming again.

-----Original Message-----
From: rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx] On Behalf Of Mark
Allen
Sent: Tuesday, October 25, 2005 9:37 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Populating fields based on field name in a file

I have a "rules" file whichcontains the following fields:

MFD - master field name (a field name in file MMST)
MFV - value for field name above 
EFL - environment file name (could be 1 of 7 for example INVMST)
EFD - environment field name (field name from file named above i.e. DEPT)
EFV - environment field value (value for field name above)

the user selects a record in the MMST file (ie. SKU: 12345) and it shoudl
then populate based on the rules each fo the fields/values in the
appropriate environment file:

Example:

Rules file:

MFD=CORG
MFV=US
EFL=INVMST
EFD=BUYR
EFV=123
So if the CORG field='US' in the SKU record selected from the MMST file I
want the BUYR field in INVMST to be set to 123

What I have "tentatively" done (and I don't like it) is shown as a "small
snippet" below, FYI In the MMST file I have 20+ fields and I have (as of
right now) 7 environment files which have multiple (5-50 approx. fields)

IF MFD='CORG' and CORG (a fld in MMST) = MFV and EFD='BUYR'
eval BUYR=EFV
endif

My "befuddled" mind right now sees that I would ahve to this the above for
EVERY field in in every file??

Gotta be an easier way???????????//



---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.