× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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;


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2024 by midrange.com 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.