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

table, and trying to automatically populate that table by using the

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

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

--