Dynamic SQL has already been noted to be one option. With that,
comes the standard warning, for taking care against SQL injection.
There is the option to divide the work into two SQL statements: the
purely DDL CREATE TABLE AS ... WITH NO DATA followed by the DML INSERT
INTO ... SELECT ... FROM. The INSERT statement does not have the
restriction for its SELECT, as diagnosed by SQL0090 for the CREATE TABLE
DDL statement does with its SELECT. I do not expect any release to
allow the host variables, as noted [though described as "parameter
markers" instead of "host variables"] in the following message:
http://archive.midrange.com/rpg400-l/200905/msg00233.html
exec sql
create table qtemp/fgShpnum as
( select a.finpalid
, cast(null as decimal(7, 0)) NbrShpnum
from dshpf05 a
) with no data
; -- original fullselect should be fine, commenting WHERE clause
-- this statement is just more succinct
exec sql
insert into qtemp/fgShpnum
with
t1 as ( select distinct a.finpalid, a.shpnum
from dshpf05 a
where date(a.assemts) between date(:fromdate)
and date(:todate)
)
select a.finpalid, decimal(count(*), 7, 0) NbrShpnum
from t1 a
group by a.finpalid
;
SQL Global Variables, available with IBM i 7.1 database SQL, may be
able to be used [in place of the disallowed Host Variables] in the WHERE
clause of the CTE of the CREATE TABLE AS.? Perhaps someone can confirm
or disprove.?
If there is concern for maintaining the two SELECT statements, e.g.
as noted in
http://archive.midrange.com/midrange-l/200509/msg01192.html
, then there is also an option to create a temporary TABLE or VIEW with
the [two date] values to be compared against in the WHERE clause. While
that would be additional DDL and DML, those would be maintained
separately from any changes to the dshpf05 on the FROM or other changes
to that one SELECT in the CREATE TABLE. And given availability of a
separate source pre-processor, some INCLUDE-like feature could enable
maintaining the same SELECT statement in one location, used for both the
CREATE TABLE and the INSERT, with macro language [e.g. "if defined"
logic] excluding all lines of the WHERE clause [with the host variables]
in the included SELECT only for the CREATE statement.
Regards, Chuck
On 11 Dec 2012 17:20, Steve Richter wrote:
the following RPGSQL is not allowed at V5R4. Is it allowed in a
newer release?
Would like to use "create table as" to create an extract table,
pulling a few rows from a large table. Then later on in the code I do
a bunch of counts, sums and joins against the smaller extract file.
I do not follow why a host variable is not allowed in this situation.
exec sql
create table qtemp/fgShpnum as (
with
t1 as ( select distinct a.finpalid, a.shpnum
from dshpf05 a
where date(a.assemts) between date(:fromdate)
and date(:todate)
)
select a.finpalid, decimal(count(*),7,0) NbrShpnum
from t1 a
group by a.finpalid )
with data ;
midrange.com
