Re: we are using Aldon Lifecycle Manager for System i - how to create a file conversion pgm

On 05-Apr-2012 06:36 , Charles Wilt wrote:

<<SNIP>>
create the statement dynamically and run it using PREPARE and
EXECUTE or just EXECUTE IMMEDIATE.

wSQLstmt =
'insert into ' + %trim(objlib) + '/' + %trim(objname)
+ ' (fld1, newfld2, fld5) '
+ ' select fld1, fld2, fld3 '
+ ' from ' + %trim(savlib) + '/' + %trim(savname)

All of that %trim work is understood to be superfluous [unless effecting removal of 0x00 characters].? I suppose to make either the variables conspicuous or the final SQL statement string "pretty", but trimming the variables achieves nothing functionally except to avoid exceeding a poorly [too small] declared string length for wSQLstmt. The SQL parser does not generally care about the so-called white-space in conspicuous contexts [actually may be required for comma as decimal point\separator]. Both of the following are acceptable, for example:

insert into LibName /TblName (fld1, newfld2, fld5)
select fld1, fld2, fld3 from LibOldNm /TblOldNm

insert into LibName / TblName (fld1, newfld2, fld5)
select fld1, fld2, fld3 from LibOldNm/ TblOldNm

Also, using overrides for the TABLE names referenced by the SQL, would allow the string to be coded directly without any variables; i.e. having moved the use of the variables to the overrides, rather than in the SQL string [where if done in CL, such CL variables have a /better/ means to be specified in commands than in the SQL]:

insert into OVERRIDDEN/TARGETNAME (fld1, newfld2, fld5)
select fld1, fld2, fld3 from OVERRIDDEN/SOURCENAME
; -- the required as prior requests to OVRDBF follow:
ovrdbf TARGETNAME tofile(LibName/TblName) ovrscope(_asrequired_)
ovrdbf SOURCENAME tofile(LibOldNm/TblOldNm) ovrscope(_asrequired_)

Regards, Chuck