On 05-Apr-2012 06:36 , Charles Wilt wrote:
create the statement dynamically and run it using PREPARE and
EXECUTE or just EXECUTE IMMEDIATE.
'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_)