INSERT INTO with CTE and no column names

[mlopez01]

Hello.

I want to use INSERT INTO with a CTE without using column names.

For example, the following works fine:

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;​

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:

exec sql
INSERT INTO QTEMP/OUTPUT
WITH CTE AS
(
... some query
)
SELECT
*
FROM ITEM
JOIN
CTE
ON FIELDA = FIELDB

If I don't specify column names for INSERT INTO and the SELECT that compile error is raised.

// 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

[paolinosal]

Hi,

How many columns have Item table?

How many columns there are from

WITH CTE(SOMEFIELD) AS
(
... some query
)
SELECT
FIELDA, FIELDB, FIELDC
FROM ITEM
JOIN
CTE
ON FIELDA = SOMEFIELD​

The total of columns of ITEM is the same of CTE?


Bye

[mlopez01]

Yes, same. I thought that was obvious. Although the CTE doesn't need to have the same number because the ITEM table is using it as a filter using a JOIN.

[Brian Rusch]

Try this:

INSERT INTO QTEMP/OUTPUT
WITH CTE(SOMEFIELD) AS
(
... some query
)
SELECT [B]A.*[/B]
FROM ITEM [B]A[/B]
JOIN CTE
ON FIELDA = SOMEFIELD​

When you do a SELECT * on a join, you get all colums from both tables, so you have to qualify the * with the table that you want.

[Brian Rusch]

Sorry, meant to bold the A but that didn't work. Here's the code again (wish I could edit my posts):

INSERT INTO QTEMP/OUTPUT
WITH CTE(SOMEFIELD) AS
(... some query)

SELECT A.*
FROM ITEM A
JOIN CTE
ON FIELDA = SOMEFIELD

[mlopez01]

Of course! As my coworker always says, "I hate sucking".

Thanks Brian.