Re: Create or replace table; WITH DATA

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.