Menu
I'm creating an SQL Query under oracle 10g, the result should give me something like that :
----------------------------------------------------------------------------------
TEXT VALUE1 VALUE2 VALUE3 VALUE4
---------------------------------------------------------------------------------
TEXT1 8795 5684 value1-value2 value3/value2*100
TEXT2 235 568 ... ...
TEXT3 125 23 ... ...
TEXT4 789 58 ... ...
TEXTN 0 0 ... ...
when i try to calculate VALUE4 column i get this error :
ORA-01476: le diviseur est égal à zéro
01476. 00000 - "divisor is equal to zero"
*Cause:
*Action:
I tried DECODE function but i stil have the same error, it's the same for CASE
NB : VALUE1, VALUE2, VALUE3 and VALUE4 are calculated columns; VALUE1 = sum(col1)+sum(col2).. and so for other VALUE2 column.
Thanks and regards
272
Method 1: SQL NULLIF Function We place the following logic using NULLIF function for eliminating SQL divide by zero error: Use NULLIF function in the denominator with second argument value zero. If the value of the first argument is also, zero, this function returns a null value.
Action: Correct the expression, then retry the operation. STUSECMTAMT. totalcr equates to 0. You could use a decode or a case to capture the 0 and dtrap the condition without aborting the SQL.
If you'd like to handle division by zero gracefully, you can use the NULLIF function. NULLIF takes two arguments: the expression of interest, and the value you want to override. If the first argument is equal to the second, then NULLIF returns NULL; otherwise, it returns the first argument.
TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotes. If you specify LEADING , then Oracle Database removes any leading characters equal to trim_character .
It all depends on whether you want to calculate a value if it would result in an infinite value or not. You can either ignore these particular instances and calculate on the remainder as Gordon's answer suggests with:
case when value2 <> 0 then value3 / value2 * 100 end
Alternatively, if want to ignore them you can use NULLIF() to change the value to NULL and not calculate anything:
value3 / nullif(value2, 0) * 100
I do not understand your contention that this being a calculated column causes an issue. If it's a virtual column then your table would never have created, as specified in the documentation a virtual column cannot refer to another by name.
If it's not a virtual column then you can do this in a select statement as normal.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
