Hello.
I want to use INSERT INTO with a CTE without using column names.
For example, the following works fine:
However I find that I must specify column names if I use an CTE or a "SQL0117 Statement contains wrong number of values" is issued.
For example:
If I don't specify column names for INSERT INTO and the SELECT that compile error is raised.
I feel like it's a syntax issue and I've tried different combinations but can't get around having to specify the field names.
Is there a way around this? For files with a lot of columns this statement becomes unwieldly.
I know it's better to specify the columns because, .... That's not my question.
Thanks in advance,
Mike
I want to use INSERT INTO with a CTE without using column names.
For example, the following works fine:
Code:
exec sql CREATE OR REPLACE TABLE QTEMP/OUTPUT LIKE ITEM ON REPLACE DELETE ROWS; // This works, no problem: exec sql INSERT INTO QTEMP/OUTPUT SELECT * FROM ITEM WHERE ISUG > 100.00;
For example:
Code:
exec sql INSERT INTO QTEMP/OUTPUT WITH CTE AS ( ... some query ) SELECT * FROM ITEM JOIN CTE ON FIELDA = FIELDB
Code:
// This works: exec sql INSERT INTO QTEMP/OUTPUT(FIELDA, FIELDB, FIELDC) WITH CTE(SOMEFIELD) AS ( ... some query ) SELECT FIELDA, FIELDB, FIELDC FROM ITEM JOIN CTE ON FIELDA = SOMEFIELD
I feel like it's a syntax issue and I've tried different combinations but can't get around having to specify the field names.
Is there a way around this? For files with a lot of columns this statement becomes unwieldly.
I know it's better to specify the columns because, .... That's not my question.
Thanks in advance,
Mike
Comment