Re: does any release allow "create table as" with a host variable?

On 13 Dec 2012 15:49, Steve Richter wrote:

On Thu, Dec 13, 2012 at 4:39 PM, CRPence wrote:

On 12 Dec 2012 09:09, Steve Richter wrote:

I really do not like it when IBM relies on a standard to
withhold a needed feature.

But what is probably one of the most non-standard SQL also
documents that "A host variable must not be: ... used in data
definition (DDL) statements such as ALTER and CREATE" so I do
not think IBM is an outlier in its similar prohibition.

but why the problem with a host variable in a create table as
statement?

exec sql
Create table qtemp/extract as
(select a.*
from BigFile a
where a.EntryDate between date(:from) and date(:to)
) with data ;

I presume there is something about the fact that the statement is DDL. And that something about the standard and\or design [possibly somewhat implied by the ANS/SQL standard] do not [desire to] enable the capability to support variables.

I was mostly just trying to show that the restriction is not unique to the DB2.

As a user of the SQL, I am faced with the same question, but am also resigned to the fact that variables are prohibited in the (select-statement) of the as-result-table clause of the CREATE TABLE statement; i.e. the documentation states explicitly that variables are disallowed:
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Statements
_i CREATE TABLE i_
The CREATE TABLE statement defines a table at the current server. The definition must include its name and the names and attributes of its columns.
"... The select-statement must not refer to [<ed:> host] variables or global variables, or include parameter markers. ..."

And of course, because what is desired can be completed without
any support for Host Variables in the the CREATE TABLE ... AS
statement, a non-standard feature of the SQL to enable that
capability would surely not be "needed"; instead merely a /wanted/
feature to [presumably just more easily] accomplish what is
desired.

the alternative requires more code, forces the programmer to "repeat
himself", making the app harder to maintain.

exec sql
create table qtemp/extract as
(select * from BigFile )
) with no data ;

exec sql
insert into qtemp/Extract
select a.*
from BigFile a
where a.EntDate between date(:fromDate) and date(:todate) ;

True. But there are surely tens of thousands of ways languages could make our lives as programmers easier; missing features that remain as one of the many wish-list items.

As I had alluded in another reply, such maintenance duplicity could be easily avoided using a macro\include language [not to imply such a feature is provided as part of the HLLs\SQL; another wish-list item left unfulfilled]:

Using inline definitions [no source INCLUDE]:

HLL-source:

%define %stmtSelect=
"select a.* ",
"from BigFile a ";
%define %stmtWhere=
"where a.EntryDate between date(:from) and date(:to)";
exec sql
Create table qtemp/extract as
(
%stmtSelect; /* SELECT for CREATE without WHERE */
) with no data ;

exec sql
insert into qtemp/extract
%stmtSelect; /* SELECT for CREATE without WHERE */
%stmtWhere; /* WHERE clause */
;

Using a shared source INCLUDE:

HLL-source:

exec sql
Create table qtemp/extract as
(
%include TheSlt(DDL); /* SELECT for CREATE without WHERE */
) with no data ;

exec sql
insert into qtemp/extract
%include TheSlt(DML);
;

INCLUDE-source:

select a.*
from BigFile a
%if %arg01="DML" %then %do;
where a.EntryDate between date(:from) and date(:to)
%end;