Re: Dynamic vs. Static SQL

On 14 Jun 2013 05:34, Charles Wilt wrote:

Never use dynamic unless you have too :)

<<SNIP>>

Off the top of my head, the only place you have to use a dynamic
statement is when you need the table name to be variable. You also
can't use a parameter here:

stmt = 'select * from ' + tableName + ' where myfld = 1';

Even that does not meet the criteria for "have to use a dynamic statement". Using an override with static SQL would be a better choice IMO. Even an effectively dynamic ALIAS would suffice [albeit I always suggest against such /temporary/ use of an ALIAS, even if created in QTEMP] in place of an override.

Before running the static SQL, assign the /variable/ table-reference; i.e. where the value of "MYTABLE" comes from whatever actual or effective variable:

OVRDBF OVERRIDDEN TOFILE(*LIBL/MYTABLE) OVRSCOPE(as_required)

*or*

CREATE OR REPLACE ALIAS OVERRIDDEN ON MYTABLE /* naming=*SYS */

Then the static SQL referencing the label OVERRIDDEN as the table-reference:

exec SQL
select * into :myDS from OVERRIDDEN where myfld = :myVar ;

Ideally the program would get compiled with the override in effect or the ALIAS existing in the appropriate library [within the library list], to enable the Access Plan for the statement to be created then [if possible], as opposed to the creation being delayed until when the program is first run.