0

I want to load a data in temp table in DB2. in syntax when i use ON COMMIT PRESERVE ROWS compiler throws an error:

Lookup Error - DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "ON" was found following "P BY ACCOUNT_NUMBER)". Expected tokens may include: "".

When I replaced On commit preserve rows by definition only temp table is successfully created but I don't see my sub query data inserted in the temp table. Can you please help me where I am making mistake?

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE AS 
(SELECT ACCOUNT_NUMBER, NET_AMOUNT
FROM SCHEMA.TABLE_NAME WHERE 1=1 AND COLUMN1='a') ON COMMIT PRESERVE ROWS;
Pang
  • 9,564
  • 146
  • 81
  • 122
Rade
  • 43
  • 2
  • 6
  • 1
    The correct statement syntax can be found [in the manual](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0003272.html?cp=SSEPGG_10.5.0%2F2-12-7-123&lang=en). It is not possible to declare a temporary table and insert data into it with one statement. – mustaccio Apr 02 '15 at 11:57
  • @mustaccio - Not on the LUW version, at least (you can on the iSeries, but that's not going to help him here). – Clockwork-Muse Apr 04 '15 at 23:58
  • Thanks mustaccio.. I created and inserted in two different comands. Worked fine wih commit on preserv rows. Thank you – Rade Apr 07 '15 at 15:42

0 Answers0