DB2 create view with common table expression

Question

Hey I exported a view definition from DB2 on AS400 and want to import it into DB2 LUW express-c, but I keep getting syntax errors whenever I try to use a WITH clause:

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=AS;MAATNUMMER, AANTAL) ;JOIN, DRIVER=4.19.77

If I execute the query in DB2 on AS400 it works and according to IBM's documentation express-c should support common table expressions.

If anyone could have a look at it and point me in the right direction

CREATE VIEW VIEWRTSGEPLAND (
    LOCATIE3,
    LOCATIE4,
    DISKNR,
    MAAT,
    MAATNUM,
    SKU,
    AANTAL,
    SOORT,
    BRON,
    DATUM,
    VOLGNR,
    STATUS,
    GEBRUIKER,
    PROGRAMMA,
    KBNUMMER,
    STATUSKB,
    "TIMESTAMP")
  AS(
      WITH 
        SEL1TF (TFLKNA, TFDSNR, TFMBKD, MAATNUMMER, AANTAL) AS (
        -- selectie op TF +  cross join NOS002 om 1 row per maat te bekomen
        SELECT TF.TFLKNA, TF.TFDSNR, TF.TFMBKD, NOS002.MAATNUMMER,
               SUM(TF.TFTA01 * NOS002.M01 + TF.TFTA02 * NOS002.M02 + 
                   TF.TFTA03 * NOS002.M03 + TF.TFTA04 * NOS002.M04 +
                   TF.TFTA05 * NOS002.M05 + TF.TFTA06 * NOS002.M06 +
                   TF.TFTA07 * NOS002.M07 + TF.TFTA08 * NOS002.M08 +
                   TF.TFTA09 * NOS002.M09 + TF.TFTA10 * NOS002.M10 + 
                   TF.TFTA11 * NOS002.M11 + TF.TFTA12 * NOS002.M12 +
                   TF.TFTA13 * NOS002.M13 + TF.TFTA14 * NOS002.M14 + 
                   TF.TFTA15 * NOS002.M15 + TF.TFTA16 * NOS002.M16) AS AANTAL
            FROM LFILES.TF TF
            CROSS JOIN AJR.NOS002 NOS002
            -- geen where, ook niet op de winkel 37 - 28
            GROUP BY TF.TFLKNA, TF.TFDSNR, TF.TFMBKD, NOS002.MAATNUMMER
        ), 
        SELTF (TFLKNA, TFDSNR, TFMBKD, MAATNUMMER, AANTAL) AS (
        -- eliminatie van de groepen die 0 zijn nu pas voor de leesbaarheid +
        -- geen 2x schrijven van de berekening
        SELECT SEL1TF.TFLKNA, SEL1TF.TFDSNR, SEL1TF.TFMBKD,
               SEL1TF.MAATNUMMER, SEL1TF.AANTAL
            FROM SEL1TF SEL1TF
            WHERE SEL1TF.AANTAL <> 0
        )
    -- main select
    SELECT E5XT.E5XTLKNR AS LOCATIE3, 
           E5XT.E5XTLKKD AS LOCATIE4, 
           SELTF.TFDSNR AS DISKNR, 
           NOS001.MAATOMSCHR AS MAAT, 
           NOS001.MAATNUM AS MAATNUM, 
           CAST((DIGITS(SELTF.TFDSNR) CONCAT '.' CONCAT DIGITS(NOS001.MAATNUM)) AS CHAR(11)) AS SKU, 
           CAST(SELTF.AANTAL AS DEC(6, 0)) AS AANTAL, 
           CAST('GEPLAND IN' AS CHAR(20)) AS SOORT, 
           CAST('power.LFILES.TF' AS CHAR(30)) AS BRON, 
           CURRENT_DATE AS DATUM, 
           CAST(0 AS DEC(9, 0)) AS VOLGNR, 
           CAST(SPACE(1) AS CHAR(1)) AS STATUS, 
           CAST(SPACE(10) AS CHAR(10)) AS GEBRUIKER, 
           CAST(SPACE(10) AS CHAR(10)) AS PROGRAMMA, 
           CAST(0 AS DEC(9, 0)) AS KBNUMMER, 
           CAST(SPACE(2) AS CHAR(2)) AS STATUSKB, 
           CURRENT_TIMESTAMP AS TIMESTAMP
      FROM SELTF SELTF
        INNER JOIN LFILES.E5XT E5XT
          ON SELTF.TFLKNA = E5XT.E5XTLKNR
        INNER JOIN AJR.NOS001 NOS001
          ON SELTF.TFMBKD = NOS001.MSMBKD
          AND SELTF.MAATNUMMER = NOS001.MAATNUMMER
);

Answer 1

Just remove the extra brackets after the first AS. I.e. this parses fine in DataStudio, so if I had the table DDL, I'm sure it would create OK.

CREATE VIEW VIEWRTSGEPLAND (
    LOCATIE3,
    LOCATIE4,
    DISKNR,
    MAAT,
    MAATNUM,
    SKU,
    AANTAL,
    SOORT,
    BRON,
    DATUM,
    VOLGNR,
    STATUS,
    GEBRUIKER,
    PROGRAMMA,
    KBNUMMER,
    STATUSKB,
    "TIMESTAMP")
  AS
    --(   <<<<< REMOVE THIS BRACKET 
WITH SEL1TF (TFLKNA, TFDSNR, TFMBKD, MAATNUMMER, AANTAL)
        AS (
            -- selectie op TF +  cross join NOS002 om 1 row per maat te bekomen
            SELECT TF.TFLKNA, TF.TFDSNR, TF.TFMBKD, NOS002.MAATNUMMER, SUM(TF.TFTA01 * NOS002.M01 + TF.TFTA02 * NOS002.M02 + TF.TFTA03 * NOS002.M03
            + TF.TFTA04 * NOS002.M04 + TF.TFTA05 * NOS002.M05 + TF.TFTA06 * NOS002.M06
            + TF.TFTA07 * NOS002.M07 + TF.TFTA08 * NOS002.M08 + TF.TFTA09 * NOS002.M09
            + TF.TFTA10 * NOS002.M10 + TF.TFTA11 * NOS002.M11 + TF.TFTA12 * NOS002.M12
            + TF.TFTA13 * NOS002.M13 + TF.TFTA14 * NOS002.M14 + TF.TFTA15 * NOS002.M15
            + TF.TFTA16 * NOS002.M16) AS AANTAL
                FROM LFILES.TF TF
                CROSS JOIN AJR.NOS002 NOS002
                -- geen where, ook niet op de winkel 37 - 28
                GROUP BY TF.TFLKNA, TF.TFDSNR, TF.TFMBKD, NOS002.MAATNUMMER
            ), 
        SELTF (TFLKNA, TFDSNR, TFMBKD, MAATNUMMER, AANTAL)
          AS (
            -- eliminatie van de groepen die 0 zijn nu pas voor de leesbaarheid +
            -- geen 2x schrijven van de berekening
            SELECT SEL1TF.TFLKNA, SEL1TF.TFDSNR, SEL1TF.TFMBKD, SEL1TF.MAATNUMMER, SEL1TF.AANTAL
            FROM SEL1TF SEL1TF
            WHERE SEL1TF.AANTAL <> 0
          )
        -- main select
        SELECT
        E5XT.E5XTLKNR AS LOCATIE3, E5XT.E5XTLKKD AS LOCATIE4, SELTF.TFDSNR AS DISKNR, NOS001.MAATOMSCHR AS MAAT, NOS001.MAATNUM AS MAATNUM, CAST((DIGITS(SELTF.TFDSNR) CONCAT '.' CONCAT DIGITS(NOS001.MAATNUM))
        AS CHAR(11)) AS SKU, CAST(SELTF.AANTAL AS DEC(6, 0)) AS AANTAL, CAST('GEPLAND IN' AS CHAR(20)) AS SOORT, CAST('power.LFILES.TF' AS CHAR(30)) AS BRON, CURRENT_DATE AS DATUM, CAST(0 AS DEC(9, 0)) AS VOLGNR, CAST(SPACE(1) AS CHAR(1)) AS STATUS, CAST(SPACE(10) AS CHAR(10)) AS GEBRUIKER, CAST(SPACE(10) AS CHAR(10)) AS PROGRAMMA, CAST(0 AS DEC(9, 0)) AS KBNUMMER, CAST(SPACE(2) AS CHAR(2)) AS STATUSKB, CURRENT_TIMESTAMP AS TIMESTAMP
        FROM SELTF SELTF
        INNER JOIN LFILES.E5XT E5XT
        ON SELTF.TFLKNA = E5XT.E5XTLKNR
        INNER JOIN AJR.NOS001 NOS001
        ON SELTF.TFMBKD = NOS001.MSMBKD
        AND SELTF.MAATNUMMER = NOS001.MAATNUMMER
--)  <<<<< REMOVE THIS BRACKET
;