Insert is not working with Select from OLD TABLE in DB2

DECLARE GLOBAL TEMPORARY TABLE
        SESSION.TABLE1
        (
             PHYSCL_OBJ_ID      BIGINT
             ,ID            INT
        )WITH REPLACE
        ON COMMIT PRESERVE ROWS NOT LOGGED;

DECLARE GLOBAL TEMPORARY TABLE
        SESSION.TABLE2
        (
             PHYSCL_OBJ_ID      BIGINT
             ,ID            INT
        )WITH REPLACE
        ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO  SESSION.TABLE1 VALUES (1,1),(2,2),(3,3);

INSERT INTO SESSION.TABLE2
        (
             PHYSCL_OBJ_ID
        )
SELECT PHYSCL_OBJ_ID 
FROM OLD TABLE 
( 
    DELETE FROM SESSION.TABLE1 GTT WHERE GTT.PHYSCL_OBJ_ID IN (1,2) 
);

INSERT INTO is not can anyone explain and help?

Below is the error message:

>[Error] Script lines: 1-7 --------------------------
 DB2 SQL Error: SQLCODE=-20165, SQLSTATE=428FL, SQLERRMC=null, DRIVER=3.68.61

Answer

Try this:

WITH D AS 
(
  SELECT PHYSCL_OBJ_ID 
  FROM OLD TABLE 
  ( 
    DELETE FROM SESSION.TABLE1 GTT WHERE GTT.PHYSCL_OBJ_ID IN (1,2)
  )
)
SELECT COUNT(1)
FROM NEW TABLE 
(
  INSERT INTO SESSION.TABLE2 (PHYSCL_OBJ_ID)
  SELECT PHYSCL_OBJ_ID FROM D
);

Advertisement

Answer