Re: Create or replace table; WITH DATA

Hi Sue,

It says: PTF 5770SS1-SI60425 V7R1M0 not available electronically.
Is it generally available or is it only in test and I have to call IBM and
request access to it?


Yours truly,

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


On 28 August 2016 at 21:38, Sue Romano <slromano@xxxxxxxxxx> wrote:

For 7.1, try PTF SI60425

Sue Romano
IBM i SQL Development

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

--

Sue Romano
IBM i SQL Development, Dept 45X
253-0284


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