DB2 CTAS Token Error

Question

I am trying to use a CTAS statement in DB2 version 11.1.0 which creates a new table and inserts it. The query is the following:

      CREATE TABLE MY_SCRATCH.LC$U7OB81478732948714_zero_to_3 AS (
      WITH two AS (SELECT id AS the_num FROM users WHERE id = 2)
        ,  one_two AS (
        SELECT id AS the_num FROM users WHERE id = 1
        UNION ALL
        SELECT * FROM two tmp
      )
        ,  zero_one_two AS (
        SELECT id-7 AS the_num FROM users where id = 7
        UNION ALL
        SELECT * FROM one_two tmp

      )
        SELECT * FROM zero_one_two tmp
        UNION ALL
        SELECT id AS the_num FROM users WHERE id = 3
      ) WITH DATA

However, I am receiving the following error:

"my_error":"SQL Error: derived_table zero_to_3 creation failed: SQL Error in CREATE TABLE as SELECT: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=AS;RO_TO_3\" (\nWITH TWO;JOIN, DRIVER=4.16.53"

According to DB2 docs, the error is because of the following:

A syntax error was detected where the symbol "token" occurs in the SQL statement. The list of symbols that might be legal shows some alternate symbols that could possibly be correct at that point, if the preceding part of the statement is entirely correct.

So I ran the above query in RazorSQL, and it threw the same error. Not quite sure where the token issue is

Answer 1

You can't use a common table expression. If you look at the syntax for CREATE TABLE (the graph below is abridged for your specific question):

>>-CREATE TABLE--table-name------------------------------------->

>--+-----------------------+--AS--(--fullselect--)-------------->
   |    .-,-----------.    |                         
   |    V             |    |                         
   '-(----column-name-+--)-'                         

>--+-WITH NO DATA-+---------------------------------------------|
   '-WITH DATA----'   

A fullselect is a portion of a full select query, but it doesn't include the common table expression. Common table expressions are part of the select-statement.

It may be possible to rewrite your query using nested table expressions instead of common table expressions, but with your example query it's hard to illustrate this given that you don't even really need common table expressions at all. Your query could be written in a far simpler manner as:

CREATE TABLE MY_SCRATCH.LC$U7OB81478732948714_zero_to_3 AS (
    select id as the_num from users where id = 2
    union all
    select id as the_num from users where id = 1
    union all
    select id-7 as the_num from users where id = 7
    union all
    select id as the_num from users where id = 3
)
   WITH DATA;