Nope same error in Db2 13:
DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM
CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED
DSNT418I SQLSTATE = 428FL SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOP0 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email:
R.Boxwell@seg.deWeb
http://www.seg.deLink zur Datenschutzerklärung
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich
Original Message:
Sent: 9/27/2022 6:59:00 PM
From: Peter Schwarcz
Subject: RE: Using FINAL TABLE with CTE INSERT statement?
Hello Venkat,
I wrote and tested my version of the query using db2 luw v11.5 which allows the SQL construct.
I only have access to a z/OS v10 sub-system that shows the same error that you get.
As Roy pointed out you can use the data returned in the SQLCA or GET DIAGNOSTICS v_rows = ROW_COUNT to get the number of rows after the insert.
Can someone check if the restriction that throws the SQLCODE = -20165 is removed in dB2 z/OS V12 or V13 ?
Regards
------------------------------
[Peter][Schwarcz]
------------------------------
Original Message:
Sent: Sep 27, 2022 10:16 AM
From: Venkat Sundar
Subject: Using FINAL TABLE with CTE INSERT statement?
Thanks Peter, that's what I was looking for. But it ended with error;
SELECT COUNT(*) FROM FINAL TABLE ( 00010007 INSERT INTO DAYS_TABLE 00020006-- ( DATE_COL ,DAYOFWEEK_ISO ,DAYNAME ,DAY_NUM) 00030005 WITH ALL_DAYS(DT, T, DS ) AS 00040000 ( SELECT DATE('1901-01-01'), 1, DATE('0001-01-01') FROM 00050000 SYSIBM.SYSDUMMY1 00060000 UNION ALL 00070000 SELECT DT + 1 DAY ,T + 1, DS FROM ALL_DAYS WHERE T < 100 ) 00080000 SELECT CHAR(DT,ISO) , DAYOFWEEK_ISO(DT) , 00090000 SUBSTR('MONTUEWEDTHUFRISATSUN',(DAYOFWEEK_ISO(DT)*3)-2,3) , DAYS(DT) 00100000 - DAYS(DS) + 1 FROM ALL_DAYS 00110000 ) 00120007 ; 00130000---------+---------+---------+---------+---------+---------+---------+---------+DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED DSNT418I SQLSTATE = 428FL SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXOP0 SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
------------------------------
Venkat Sundar
Original Message:
Sent: Sep 27, 2022 01:56 AM
From: Peter Schwarcz
Subject: Using FINAL TABLE with CTE INSERT statement?
Is there some reason that you do not want to use the Final Table SQL construct ?
The following SQL does pretty much what you are lookin for:
1. A recursive sql to generate 100 rows
2. Insert into a table days_table
3. Use the final table to return the number of rows that just got inserted.
SELECT count(*) FROM FINAL TABLE (insert into days_table ( date_col ,dayofweek_iso ,DayName ,day_num ) WITH ALL_DAYS(DT, t, DS ) AS ( SELECT DATE('1901-01-01'), 1, DATE('0001-01-01') FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT DT + 1 DAY ,t + 1, DS FROM ALL_DAYS WHERE T < 100 ) seLECT char(dt,iso) , dayofweek_iso(dt) , substr('MonTueWedThuFriSatSun',(dayofweek_iso(dt)*3)-2,3) , days(DT) - days(DS) + 1 FROM ALL_DAYS )
Regards
------------------------------
[Peter][Schwarcz]
Original Message:
Sent: Sep 26, 2022 10:11 PM
From: Venkat Sundar
Subject: Using FINAL TABLE with CTE INSERT statement?
Hi there, I am using a CTE query to insert rows into a table. The output does not give the number of rows inserted. FINAL TABLE is also not allowed in this case, any thoughts how to get the rows inserted ?
------------------------------
Venkat Sundar
------------------------------