Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Unanswered: SQL Query-Translate Selected data into INSERT statement

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the table that you are trying to insert this data into, what does it look like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    7

    Post r937 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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please restate what your table looks like, and this time please give the table name, the column names, and the column datatypes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    7

    r937 could you please restate what your table looks like, and this time please give

    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since your SELECT works perfectly, so should this --
    Code:
    INSERT
      INTO salesqry2
         ( DDAITX
         , DHA3CD
         , January
         , February
         , March
         , April
         , May
         , June
         , July
         , August
         , September
         , October
         , November
         , December )
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2011
    Posts
    7
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    should it, by chance, say library1/salesqry2 instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2011
    Posts
    7
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by 1brandeja5 View Post
    It needs to take the selected data from library1/salesqry1 and place it into curtlibt/salesqry2.
    so did you try running my query with this specific change?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2011
    Posts
    7
    Yes, unfortunately still the same results

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what are the datatypes of DDAITX in curtlibt/salesqry2 and itemnumber in library1/salesqry1 ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by 1brandeja5 View Post
    After attempting with this syntax, it gives me the following error:

    SQL7008 : Position 1 SALESQRY2 in CURTLIBT not valid for operation.
    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

  14. #14
    Join Date
    Aug 2011
    Posts
    7

    Problem solved

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •