Running v7.2 TR 3
DB group 9

trying to use
CREATE OR REPLACE TABLE mytbl as
(select <..>
) WITH DATA;

I get an error...
SQL0109 - WITH DATA clause not allowed.

If I remove the OR REPLACE, it works fine.

If I do the CREATE OR REPLACE and remove the WITH DATA, I get:
SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: DATA WITH
DEFINITION.

Ookkay...so lets do what I'm told
CREATE OR REPLACE TABLE mytbl as
(select <..>
) DATA WITH DEFINITION;

SQL0104 - Token WITH was not valid. Valid tokens: INITIALLY.

Ookkay...so lets do what I'm told
CREATE OR REPLACE TABLE mytbl as
(select <..>
) DATA INITIALLY WITH DEFINITION;

SQL0104 - Token WITH was not valid. Valid tokens: DEFERRED.

Wait a minute...that's MQT syntax...

Looking at the manual, there's no example. But I do see an interesting
clause ON REPLACE xxxx.
CREATE OR REPLACE TABLE mytbl as
(select <..>
) WITH DATA
ON REPLACE DELETE ROWS;

Yeah, that worked.

The picture on the developerWorks web site highlights the need for it.
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Create%20OR%20REPLACE%20Table

Charles

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.