Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: SQL Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);

    Hi All,

    I am getting this error when i execute these stmts in sql developer

    CREATE TABLE UPC (sap_sold_to VARCHAR(20), legacy_ship_to INTEGER, company_code VARCHAR(20), document_date DATE, sap_document_no VARCHAR(20),
    ref_legacy_doc VARCHAR(20), amount DECIMAL(10,2), po_no VARCHAR(20), carrier_name VARCHAR(20), account_at_customer SMALLINT,
    retail_price DECIMAL(10,2), retail_pieces SMALLINT, upc BIGINT, description VARCHAR(100), unit_pack SMALLINT, wholesale_units_pcs SMALLINT,
    wholesale_price DECIMAL(10,2), wholesale_extension DECIMAL(10,2), error_log VARCHAR(1000) DEFAULT NULL);

    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2-27-2012', 'TBD', 9578441, 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, );

    This is the error


    Error starting at line 6 in command:
    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2-27-2012', 'TBD', 9578441, 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, )
    Error at Command Line:6 Column:0
    Error report:
    SQL Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);', 3, 1, 7.94, 7.94,;<rvc_element>, DRIVER=3.63.123

    Please give your feedback

    Thanks.

  2. #2
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    your values don't match your data types

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you specify a column in the insert statement, you must provide a value for it.

  4. #4
    Join Date
    Jun 2012
    Posts
    5
    Hi Andy,

    Can you tell me which data does not match. I could not see anything, Sorry.

    Also, in the sql stmt

    error_log VARCHAR(1000) DEFAULT NULL);

    should be

    error_log VARCHAR(1000) );

    I was just trying something.

    Thanks.

  5. #5
    Join Date
    Jun 2012
    Posts
    5
    Hi n_i

    The value for the "error_log" is empty.

    Basically if there is no error, this column values should be empty.

    Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ven12344 View Post

    The value for the "error_log" is empty.
    I guess the problem is that your notion of "empty" differs from what DB2 knows as "empty".

  7. #7
    Join Date
    Jun 2012
    Posts
    5
    Hi n_i,

    Can you please let me know what data should be given for that column.

    Thanks.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see Manuals(e.g. Infromation Center).

    INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-'   
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                 
    
    ...

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And, I doubt some format error of datetime value, datatype mismatch, or overflow, so on...
    depending on your environment(e.g. old DB2 version/release, setting of local date/time format).
    VALUES ('TBD', 053900015, 'AG US', '2-27-2012', 'TBD', 9578441, 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, )

  10. #10
    Join Date
    Jun 2012
    Posts
    5
    Hi Tonkuma

    I have changed the data in the value and also changed "SMALLINT" to "INTEGER"

    Please find the sql stmt again

    CREATE TABLE UPC (sap_sold_to VARCHAR(20), legacy_ship_to INTEGER, company_code VARCHAR(20), document_date DATE, sap_document_no VARCHAR(20),
    ref_legacy_doc VARCHAR(20), amount DECIMAL(10,2), po_no VARCHAR(20), carrier_name VARCHAR(20), account_at_customer INTEGER,
    retail_price DECIMAL(10,2), retail_pieces SMALLINT, upc BIGINT, description VARCHAR(100), unit_pack SMALLINT, wholesale_units_pcs SMALLINT,
    wholesale_price DECIMAL(10,2), wholesale_extension DECIMAL(10,2), error_log VARCHAR(1000) DEFAULT NULL);

    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2/27/2012', 'TBD', 9578441, 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, );

    This is the errror I get in SQL Developer, and I am using DB2 10.1 Express C

    Error starting at line 6 in command:
    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2/27/2012', 'TBD', 9578441, 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, )
    Error at Command Line:6 Column:0
    Error report:
    SQL Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);', 3, 1, 7.94, 7.94,;<rvc_element>, DRIVER=3.63.123

    Thanks.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please read carefully the suggestions already posted.
    Quote Originally Posted by n_i View Post
    I guess the problem is that your notion of "empty" differs from what DB2 knows as "empty".
    Quote Originally Posted by tonkuma View Post
    Please see Manuals(e.g. Infromation Center).

    INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-'   
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                 
    
    ...

  12. #12
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If you cannot see where the insert does not match the create, suggest you change both to show the columns vertically rather than horizontally (no neeed to post this, just look at them side-by-side so you can see any mismatch).

  13. #13
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    don't seem to be making much ground here so
    have a look at this.....
    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2/27/2012', 'TBD', '9578441', 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94, default );

    or this .......
    INSERT INTO UPC (sap_sold_to, legacy_ship_to, company_code, document_date, sap_document_no, ref_legacy_doc, amount, po_no, carrier_name,
    account_at_customer, retail_price, retail_pieces, upc, description, unit_pack, wholesale_units_pcs, wholesale_price, wholesale_extension,
    error_log) VALUES ('TBD', 053900015, 'AG US', '2/27/2012', 'TBD', '9578441', 615.99, 'N/A', 'UPS', 39500, 5.29, 3, 0067008886722,
    'GIBSON EASTER COUNTE', 3, 1, 7.94, 7.94 );

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Initially, you had this:
    Code:
    7.94, );
    This is a syntax error because no value (and no NULL and no DEFAULT clause) have been provided after the comma and before the closing ')'. If you want to have an empty string, use '' - nothing else can be empty anyway. Note that NULL is not "empty".

    I suggest that you start with a table and a single column only, attempt an insert matching to the existing column. Then you add the next column, adjust the insert statement, then the next column, and so on... until you hit the problem and then you know it is the last column you added. (Basically, just apply usual debugging techniques.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    there was also a type mismatch for the sap_document_no column. Not sure why it's a varchar when it's apparently going to be a number

Posting Permissions

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