Conversion Error On Assignment To Column Db2
Contents |
(troubleshooting) Problem(Abstract) The example in this document illustrates how a seemingly correct IBM SQL/400 statement involving numeric calculations can fail with error SQL0406 - Conversion error on assignment to column. Resolving the problem The following example as400 sqlcode -204 illustrates how a seemingly correct IBM SQL/400 statement involving numeric calculations can fail with the
Sqlcode 407 Sqlstate 23502
following message: SQL0406 - Conversion error on assignment to column. This is the correct behavior based on scale and precision rules for
Sqlcode 206
SQL decimal operations. 1 Create the table: CREATE TABLE QTEMP/XVALUE (XVALUE NUMERIC ( 30, 9) NOT NULL WITH DEFAULT) 2 Insert a value: INSERT INTO QTEMP/XVALUE VALUES(0) 3 Attempt to update the value with the following statement: UPDATE
Sqlcode=-104, Sqlstate=42601
QTEMP/XVALUE SET XVALUE = (1 + (10.25 /100)) * 69.50 The SQL statement fails with message SQL0406. However, the following statement will successfully insert the value: UPDATE QTEMP/XVALUE SET XVALUE = (1 + (10.25 /100.00)) * 69.50 Why? The following Decimal Arithmetic in SQL rules are documented in the SQL Reference under the section on Expressions. The following formulas define the precision and scale of the result of decimal operations in SQL. The symbols p sql error and s denote the precision and scale of the first operand and the symbols p' and s' denote the precision and scale of the second operand. Addition and Subtraction The scale of the result of addition and subtraction is max (s,s'). The precision is min(31,max(p-s,p'-s') +max(s,s')+1). Multiplication The precision of the result of multiplication is min (31,p+p') and the scale is min(31,s+s'). Division The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative. Now, apply these rules to the calculation in question: (1 + (10.25 / 100)) * 69.50 The first evaluated is 10.25/100: p=4, s=2, p'=3, s'=0 so p,s = 31, (31-p+s-s') = 31, (31-4+2+0) = 31, 29 The next evaluated is 1+(above result): so p=1, s=0, p'=31, s'=29 so p,s = min(31,max(p-s,p'-s') +max(s,s')+1), max(s,s') = min(31,max(1-0,31-29) +max(0,29)+1), max(0,29) = min(31,max(1,2) + 29 +1), 29 = min(31,2 + 30), 29 = min(31,32), 29 = 31, 29 Finally evaluated is the above result * 69.50: so p=31, s=29, p'=4, s'=2 so p,s = min(31,p+p'), min(31,s+s') = min(31,31+4), min(31,29+2) = min(31,35), min(31,31) = 31,31 Therefore, the final precision and scale of the operation is
Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > SQL0406 Conversion Error in AS/400 DB2 from Java Want to Advertise Here? Solved SQL0406 Conversion Error in AS/400 DB2 from Java Posted on 2010-01-22 Operating Systems DB2 Java 1 Verified Solution 5 Comments 3,314 Views Last Modified: 2013-12-06 1) We have database schema in iseries AS/400 DB2 V5R3 2) On a insert statement we are getting http://www.ibm.com/support/docview.wss?uid=nas8N1016979 error SQL0406 Conversion Error on assignment to column. 3) We know the column and same column DECIMAL(13,2) is in 2 tables. We are taking value from 1st table column via screen and Java code where we apply BigDecimal and scale and inserting into 2nd table column. 4) There are no calculations/formulae in between. One case where this error may occur is we exceed the digits before decimal point allowed i.e. 11. But this huge a number is https://www.experts-exchange.com/questions/25077720/SQL0406-Conversion-Error-in-AS-400-DB2-from-Java.html unable to occur. 5) After decimal we can have 2 digits. In Linux DB2, if we give more than 2 also, truncation to 2 digits occurs and insert is successful. Is this the case in AS/400 DB2. We do not have access here to test. 6) 1 particular value which failed is 53489.93. Since value is taken from source column DECIMAL(13,2) and inserted in target column DECIMAL(13,2) it looks straight. 7) But the devil must be lurking in JDBC driver jt400.jar or Java code where they convert value to BigDecimal and set scale upto 2 digits. 8) The value of the column TOTAL_PREMIUM is exceeding normal length and hence: TOTAL_PREMIUM FOR COLUMN FLD03430 DECIMAL(13, 2) NOT NULL DEFAULT 0 It is exactly same way in both tables. Could this be the culprit in any way? 0 Question by:k_murli_krishna Facebook Twitter LinkedIn Google LVL 34 Active today Best Solution byGary Patterson This might be easier if you showed us the problem code and schemas of the two tables. At least post the final SQL that is getting passed to the AS/400 for execution. You can run an SQL Database Monitor Go to Solution 5 Comments LVL 34 Overall: Level 34 Operating Systems 21 DB2 14 Java 7 Message Active today Accepted Solution by:Gary Patterson2010-01-22 This might be easier if you showed us the problem code and schema
and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor. http://forum.spring.io/forum/spring-projects/data/69366-ibatis-conversion-error-on-db2 Announcement Announcement Module Collapse No announcement yet. Ibatis conversion error http://conversion.error.on.assignment.to.column.winfaults.net/ on DB2 Page Title Module Move Remove Collapse X Conversation Detail Module Collapse Posts Latest Activity Search Forums Page of 1 Filter Time All Time Today Last Week Last Month Show All Discussions only Photos only Videos only Links only Polls conversion error only Filtered by: Clear All new posts adi203793 Junior Member Join Date: Sep 2009 Posts: 1 #1 Ibatis conversion error on DB2 Sep 3rd, 2009, 03:48 AM I have been using Ibatis through Spring to acccessing data from DB2 to java. I have been run this query so many times sometimes it conversion error on works and sometimes not. means I m not able to replicate it.while updating the record I got the error like this: UPDATE STHEDRL1 SET STHRIN=? WHERE STHINT=? AND STHNUM=? AND STHSCD=? AND STHADT=? Types: [java.lang.String, java.lang.String, java.lang.Integer, java.lang.String, java.lang.Integer] ERRORID :org.springframework.dao.DataIntegrityViolationExc eption --EXCEPTION DETAILS-- Message : SqlMapClient operation; SQL []; --- Cause: java.sql.SQLException: [SQL0302] Conversion error on host variable or parameter *N.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLExceptio n: --- The error occurred while applying a parameter map. --- Check the ShopDatesEntrySqlMap.updateRingForUltraInspectionR ecord-InlineParameterMap. --- Check the statement (update failed). Please send ur suggesstion.Thanks in advance. and after few days again I got the error:which I have never received earlier.we have used this query so many times and after few minuts its working fine again.Please send ur suggesstion.Thanks in advance. Cause: java.sql.SQLException: [SQL0406] Conversion error on assignment to column TRNPTM. at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.translate(SQLStateSQLExceptionTransl ator.java:111) at org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.translate(SQLErrorCodeSQLExcepti onTranslator.java:322) at org.springframework.orm.ibatis.SqlMapClientTemplat e.execute(SqlMapClientTemplate.java:212) at org.springframework.orm.ibatis.SqlMapClientTemplat e.insert(SqlMapClientTemplate.java:397) Caused by: com.ibat