I am having difficulty inserting the data I have received from a select query into an existing file. This is what I currently have:

SELECT itemnumber, warehouse,
SUM(CASE InvMONTH WHEN 1 THEN DDARQT01 ELSE 0 END) AS January,
SUM(CASE InvMONTH WHEN 2 THEN DDARQT01 ELSE 0 END) AS February,
SUM(CASE INVMONTH WHEN 3 THEN DDARQT01 ELSE 0 END) AS March,
SUM(CASE INVMONTH WHEN 4 THEN DDARQT01 ELSE 0 END) AS April,
SUM(CASE INVMONTH WHEN 5 THEN DDARQT01 ELSE 0 END) AS May,
SUM(CASE INVMONTH WHEN 6 THEN DDARQT01 ELSE 0 END) AS June,
SUM(CASE INVMONTH WHEN 7 THEN DDARQT01 ELSE 0 END) AS July,
SUM(CASE INVMONTH WHEN 8 THEN DDARQT01 ELSE 0 END) AS August,
SUM(CASE INVMONTH WHEN 9 THEN DDARQT01 ELSE 0 END) AS September,
SUM(CASE INVMONTH WHEN 10 THEN DDARQT01 ELSE 0 END) AS October,
SUM(CASE INVMONTH WHEN 11 THEN DDARQT01 ELSE 0 END) AS November,
SUM(CASE INVMONTH WHEN 12 THEN DDARQT01 ELSE 0 END) AS December
FROM library1/salesqry1
WHERE invoicemonth <> ''
GROUP BY itemnumber, warehouse
ORDER BY itemnumber, warehouse;


This select statement works perfectly, but I need to translate this into an insert to automate this process.

Please assist.

Thank you

the table that you are trying to insert this data into, what does it look like?

It is blank, but appears in this form:
LEN DEC
DDAITX Item number 15
DHA3CD Warehouse 3
JANUARY 31 3
FEBRUARY 31 3
MARCH 31 3
APRIL 31 3
MAY 31 3
JUNE 31 3
JULY 31 3
AUGUST 31 3
SEPTEMBER 31 3
OCTOBER 31 3
NOVEMBER 31 3
DECEMBER 31 3

The data types appear to be correct based on the data I am trying to insert.

I run a query before this one to validate the data is compatible value.

Thanks

could you please restate what your table looks like, and this time please give the table name, the column names, and the column datatypes

I apologize, here is the correct format:

table salesqry2
DDAITX nvarchar(15) NOT NULL,
DHA3CD nchar(3) NOT NULL,
January decimal(31,3),
February decimal(31,3),
March decimal(31,3),
April decimal(31,3),
May decimal(31,3),
June decimal(31,3),
July decimal(31,3),
August decimal(31,3),
September decimal(31,3),
October decimal(31,3),
November decimal(31,3),
December decimal(31,3)

This table is rolling 12 month sales report that has been pivoted from its original form.

Please let me know if you have any other questions.

since your SELECT works perfectly, so should this --

After attempting with this syntax, it gives me the following error:

SQL7008 : Position 1 SALESQRY2 in CURTLIBT not valid for operation.

I appreciate your input, r937.

should it, by chance, say library1/salesqry2 instead?

It needs to take the selected data from library1/salesqry1 and place it into curtlibt/salesqry2.

I have validated that the file is empty and is structured as I listed it below.

Does the keyword Values need to be placed anywhere? Also, would I need to specify a table alias in this example?

Thanks.

so did you try running my query with this specific change?

Yes, unfortunately still the same results

what are the datatypes of DDAITX in curtlibt/salesqry2 and itemnumber in library1/salesqry1 ?

A quick (google) search for the error message seems to indicate that there is a problem with the DB2 configuration:

http://www.querytool.com/help/876.htm
http://publib.boulder.ibm.com/infoce...atalogtbls.htm

So the table is not journaled and you need to change that.

See the manual:
http://publib.boulder.ibm.com/infoce...jrnalssrc.html

You were on to it with the data types of the incoming file (library1/salesqry1).

The field "INVMONTH" was a 2 character (Char) type, so I needed to modify the values to 01, 02, 03, 04, 05, 06, 07, 08, 09 in the case statements.


Thank you for all of your assistance!!