DB2 Using With Clause within inserts in stored procedure

mastermistik 0
Viewed 30

I am using the below code inside the stored procedure. It is working fine as long as I am not using the block for TEMP_3. I got such like an error

The data type for parameter or SQL variable "'WITH TEMP AS ( SELECT * FROM TABLEE" is not supported in the routine, compound SQL statement, or parameter list of a cursor value constructor.. SQLCODE=-789, SQLSTATE=429BB, DRIVER=4.28.11

DECLARE C1 CURSOR FOR S1;
'WITH TEMP AS (
        SELECT *
        FROM TABLEE 
        WHERE ID = 2
        )), 
        TEMP_1 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_A (Col_1, Col_2)
        SELECT Col_1, 'A'
        FROM TABLE_A
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_2 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_B (Col_1, Col_2)
        SELECT Col_1, 'B'
        FROM TABLE_B
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_3 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_C (Col_1, Col_2)
        SELECT Col_1, 'C'
        FROM TABLE_C
        JOIN TABLEE ON ID = Col_1
        )),
        SELECT 1
        FROM SYSIBM.SYSDUMMY1';
  OPEN C1;
  CLOSE C1;
db2 stored-procedures
1 Answers

Too many errors.
Just one target table example.

BEGIN
  DECLARE C1 CURSOR FOR S1;
  PREPARE S1 FROM 
  'WITH TEMP AS 
  (
    SELECT *
    FROM TABLEE 
    WHERE ID = 2
  ), 
  TEMP_1 AS 
  (
     SELECT COUNT(1) AS ID 
     FROM NEW TABLE 
     (
        INSERT INTO TABLE_A (Col_1, Col_2)
        SELECT Col_1, ''A''
        FROM TABLE_A
        JOIN TABLEE ON ID = Col_1
     )
  )
  SELECT 1
  FROM SYSIBM.SYSDUMMY1';
  OPEN C1;
  CLOSE C1;
END

Please, provide full example with the corresponding CREATE TABLE and INSERT INTO statements as well as in this example.

Mark Barinstein0
Related