Sure static SQL in composition with overriding tables still can be used or
creating an alias.
I could also imagine other scenarios with SQL (compositions of views,
indexes and global variables) even though I'd have to test them because I'm
not sure about performance.

What I meant is, the following is not possible:

Exec SQL Select ... into :HostDSFile
From :MyHostFile

I've seen it quite often and get asked why it won't work.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Saturday, 07.4 2012 00:04
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: AW: Question on updating some OLD programs

On 05-Apr-2012 22:52 , Birgitta Hauser wrote:

Use dynamic SQL to access whatever table/file/view you want.
Static SQL is not possible, because setting tables/files and views
with host variables is NOT allowed.

Suggesting that the use of "static SQL is not possible" in the context of
the wider discussion is too absolute IMO; too specific to the comment about
the use of host variables. The two quoted statements seem, generally,
similar to having suggested that the use of RPG is not possible without the
use of the EXTFILE and EXTDESC given the same situation. So hoping to be
more clear for anyone less acquainted with the SQL, for whomever might
improperly infer the "not possible" comment to be a more general implication
about what can [not] be accomplished with static SQL...

While not possible to use Host Variables to replace TABLE references in a
[DML] SQL statement, dynamic reference to TABLEs different than those named
in a static DML SQL statement *is still possible* to achieve; just not by
using Host Variables. The dynamic effect is easily accomplished using
overrides, or possibly with library list manipulation, or [IMO less
desirable] SQL ALIAS, in the same way that dealing with the RPG F-Spec can
be accomplished with overrides or library list manipulation without the use

For example, the following static SQL can be compiled and can produce the
desired results at run-time, without the TABLE name ONEROWONLY existing
anywhere on the system:

Exec SQL
select current_date, current_time
into :date1 , :time1

The compile of that embedded SQL statement can function without any
overrides [not merely per no dependency on any column names]. But for the
run-time, all that is required for the statement to be functional when no
TABLE named ONEROWONLY exists is to have previously effected a properly
scoped override of the label\name ONEROWONLY, to any database file name
capable of being accessed via the SQL and according to the requirements of
the statement; e.g. OVRDBF ONEROWONLY
TOFILE(SYSIBM/SYSDUMMY1) Instead of overrides, a prior SQL request to
well, to allow the statement to run without errors; though a permanent ALIAS
that existed since before compile-time would be better IMO... since
overrides are dynamic without creation of an "object".

Regards, Chuck
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.