Db2 Sql Error 801
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting db2 divide two numbers ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join db2 code -805 the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a db2 error code 805 minute: Sign up How to Divide two columns in DB2 SQL up vote 1 down vote favorite I have disk capacities stored in 2 columns as CAPACITY_TOTAL and CAPACITY_USED. I need to present % utilization for which I need to http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/codes/src/tpc/n901.html divide CAPACITY_USED by CAPACITY_TOTAL as CAPACITY_USED/CAPACITY_TOTAL * 100. I am using below Query: select CAPACITY_USED / CAPACITY_TOTAL from TableName which gives below error: DB2 SQL Error: SQLCODE=-801, SQLSTATE=22012, SQLERRMC=null, DRIVER=4.15.82 I have casted my columns to decimal but even that doesn't work. sql db2 share|improve this question edited May 4 '15 at 17:41 asked May 4 '15 at 17:36 andruX 577 1 Are you sure CAPACITY_TOTAL is always greater than 0? SQLSTATE=22012 suggests division by zero. –npe May 4 '15 at http://stackoverflow.com/questions/30036359/how-to-divide-two-columns-in-db2-sql 17:40 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted OK, so SQLSTATE=22012 means you're trying to divide by zero. Apparently some of your rows contain nulls or zeroes in CAPACITY_TOTAL column and db fails to calculate the formula. Try this query for better results: SELECT CASE CAPACITY_TOTAL WHEN 0 THEN 0 WHEN NULL THEN 0 ELSE CAPACITY_USED / CAPACITY_TOTAL END AS UTILIZATION FROM MY_TABLE You may want to put some other meaningful return values for zeroes / nulls. Alternatively, skip rows, where CAPACITY_TOTAL is null or zero: SELECT CAPACITY_USED / CAPACITY_TOTAL AS UTILIZATION FROM MY_TABLE WHERE CAPACITY_TOTAL IS NOT NULL AND CAPACITY_TOTAL > 0 share|improve this answer edited May 4 '15 at 18:07 answered May 4 '15 at 17:48 npe 10.1k12845 add a comment| up vote 3 down vote Error code 801 means your query was trying to divide by zero. select CAPACITY_USED / CAPACITY_TOTAL from TableName where CAPACITY_TOTAL IS NOT NULL and CAPACITY_TOTAL <> 0 share|improve this answer edited May 4 '15 at 17:58 answered May 4 '15 at 17:47 Pigasus 10510 1 NVL won't solve division by zero. It will only help against nulls. –npe May 4 '15 at 17:53 Oops...good catch. –Pigasus May 4 '15 at 17:55 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name
- What is sql abend code -801,-802? Previous TopicNext Topic 0 1046 views Tweet asked mar August 3, 2014 07:07 AM db2 What is sql abend code -801,-802?  add comment 1 Answers 0   http://www.expertsfollow.com/db2/what-is-sql-abend-code-801-802/questions_answers/forum/5474 answered By Mswami  0   -802Explanation:An exception error has occurred in the processing of an SQL arithmetic function or arithmetic expression. The exception error occurred in one of the following areas:In the SELECT list of an SQL SELECT statement.In the search condition of a SELECT, UPDATE, MERGE, or DELETE statement.In the SET clause of the UPDATE operation.During the evaluation of an aggregate function.Parts of exception-type, operation-type, data-type, and position-number might not db2 sql be returned to the SQL communication area, depending on where the error was detected.exception-type:Indicates the type of error.Possible values for exception-type are:FIXED POINT OVERFLOWDECIMAL OVERFLOWZERO DIVIDEDIVIDE EXCEPTIONEXPONENT OVERFLOWINVALID OPERATIONSUBNORMALUNDERFLOWOVERFLOWOUT OF RANGEoperation-type:Indicates the operation that was being performed at the time of the error. Possible operation-type includes the following arithmetic operations:ADDITIONSUBTRACTIONMULTIPLICATIONDIVISIONNEGATIONoperation-type might also be one of the following operations:Programmer response:Examine the SQL statement to see if the cause of the problem can be db2 sql error determined. The problem might be data-dependent, in which case it will be necessary to examine the data that was being processed at the time the error occurred.If the arithmetic expression in error was within the SELECT list of the outer SELECT statement, it is advisable to include an indicator variable for all expressions in the SELECT list. This allows processing to continue so that non-error column and expression values can be returned. flag    add comment Your answer Join with account you already have Please login to post your answer Preview Write A Tutorials Online-Classroom Classes www.writeabc.com Follow this question 1 person following this question Follow db2 category 1 person following this tag  Question tags Asked 1 year and 1 month ago ago Number of Views -1046 Number of Answers -1 Last updated 2 years and 2 months ago ago  Similar questions How to resolve -502 sql code in DB2?How to resolve -818 sql code in DB2?How to resolve -407 sql code in DB2?How to resolve -803 sql code in DB2?How to resolve -917 sql code in DB2?How to resolve -504 sql code in DB2?What is -904 sql code? How to resolve it?What is the meaning of -805 SQL return code?Return code 100 means?Can I code union in cobol-db2 pgm ? Ready