Re: FROM Technique? static sql, variable FROM table name

Chuck,

I usually use record level (F-spec defined) access for tables, and in the cases where I do use embedded SQL, the access is always to a specific table whether using static or dynamic SQL. I was not aware that I could define a generic table name on the SELECT statement after issuing an OVRDBF command. I tend to flip-flop between defining the overrides (when needed) in CLP or RPG IV, though I lean towards the latter more lately.

Thanks.

* Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
615.995.7024
fax
615.995.1201
email
jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



CRPence wrote:

Those are "Host Variables" which represent a replacement for a column result or compare value [as Simon already responded with a doc ref]. As such, the use of a /host variable/ for a table name is not an option. To use embedded SQL with a variable file name, code a bogus static file name, and then use an override to route the open of the static name to the variable\changing name. For example:

CLP:
dcl &histlibr *char 10 HIST#### /* #s reset to Year */
dcl &histfile *char 10 'TableName '
dcl &datetime *char 20
dcl &yearnbr *dec (4 0)
dcl &yearchar *char 4
qsys/rtvsysval qdatetime &datetime
chgvar &yearchar (%sst(&datetime 1 4))
chgvar &yearnbr &yearchar
chgvar &yearnbr (&yearnbr - 1)
chgvar &yearchar &yearnbr
chgvar (%sst(&histlibr 5 4)) &yearchar
qsys/ovrdbf "CurrHist" TOFILE(&histlibr/&histfile) +
ovrscope(*calllvl) /* set scope as required */
call mySQLRPGpgm /* changed to access "CurrHist" +
Select ... From "notAlibr"/"CurrHist" */

If the SQL program is already being called by a CLP, then something like the above is a very simple change. If the active history library name is stored somewhere, then that stored value can be updated as part of the process of /renaming/ the history library, thus extracted versus calculated in the sample CLP.

If not called by a CLP, then as a SQL RPG using a string for a dynamic override request issued via QCMDEXC from within the RPG program, setting a variable to the prior year to be concatenated into the override command string would suffice just as well as within a CLP. Probably easier in RPG, but generating the library name can even be done easily in a SQL SET or SQL VALUES statement:

Exec SQL
Values 'HIST' CONCAT
digits(decimal(year(current date)-1, 4, 0))
Into :HistLibr;

Exec SQL
Set :HistLibr = 'HIST' CONCAT
digits(decimal(year(current date)-1, 4, 0)) ;

Regards, Chuck