MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

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



fixed

On Thu, Dec 13, 2012 at 4:39 PM, CRPence <CRPbottle@xxxxxxxxx> 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 * from BigFile a
where a.EntryDate between date(:from) and date(:to)
) with data ;


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 * from BigFile a
where a.EntDate between date(:fromDate) and date(:todate) ;





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact