× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.