Re: Create or replace table; WITH DATA

That's strange...
Back in March,
http://archive.midrange.com/midrange-l/201603/msg00480.html

CREATE OR REPLACE TABLE mytbl as
(select <..>
) WITH DATA
ON REPLACE DELETE ROWS;

Ran on a 7.2 box for me...but now I get the error you do.

Note that while it ran, it didn't actually replace the rows for me if the
table exists.

I had a PMR open...but got busy and IBM closed it. I've yet to create a
new one.

Charles

On Fri, Aug 26, 2016 at 11:17 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

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
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
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.