Do you have 7.3 PTF SI60426? That is a fix for CREATE OR REPLACE TABLE
with a 3-part name in the AS clause.

WITH DATA and ON REPLACE DELETE ROWS should work (and it does for me).

Apparently there's an issue with using the "or replace" option on create
table, and trying to automatically populate that table by using the "with

data" clause.

Create or Replace table qtemp.PTF_INFO as (
select * from GDWEB.QSYS2.PTF_INFO)
with data

SQ20038
Clauses not valid in same definition.
PRESERVE and WITH DATA cannot be specified at the same time.
The WITH DATA option cannot be specified with ON REPLACE PRESERVE ROWS.

Hmm, must be a default.

Create or Replace table qtemp.PTF_INFO as (
select * from GDWEB.QSYS2.PTF_INFO)
with data
on replace delete rows

SQL0104
Token REPLACE was not valid. Valid tokens: COMMIT.

I suppose I could do the
DROP TABLE
first and stop using "or replace"
but I'd rather try to get the syntax right than using a work around.

IBM i 7.3


Rob Berendt


Sue Romano
IBM i SQL Development



This thread ...

Follow-Ups:

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

This mailing list archive is Copyright 1997-2019 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].