Re: Create or replace table; WITH DATA

On 7.1, I get the same error with a two-part name in the AS clause.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 26 August 2016 at 13:09, Sue Romano <slromano@xxxxxxxxxx> wrote:

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 is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.