# handle divided by zero error in calculated item

hi
i have oracle form , 2 columns
:SHIP_DET.U_PRICE
and :SHIP_DET.COST_PRICE
i have calculated column , which i need to have something like this :
``````SELECT nvl(:SHIP_DET.U_PRICE / nullif(:SHIP_DET.COST_PRICE, 0),0) FROM DUAL
``````

how do i put this formula in the folmula of that item
Asked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try this

CASE WHEN
nullif(:SHIP_DET.COST_PRICE, 0) = 0 THEN 0
ELSE :SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE END

mlmcc
Author Commented:
Error as per attached
Untitled.png
Commented:
Did you try to run that statement or did you put it into your SELECT?

SELECT
CASE WHEN
nullif(:SHIP_DET.COST_PRICE, 0) = 0 THEN 0
ELSE :SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE
END
FROM Dual

mlmcc
Author Commented:
Dear , it is not a select statement , I'm trying to put the formula in property item for the calculated item in the form
programmer-analystCommented:
You can try this:
1) create a function
FUNCTION f_1 RETURN number IS
n_ret   number;
BEGIN
IF nvl(:SHIP_DET.U_PRICE,0)=0 THEN
n_ret := 0;
ELSE
n_ret:=:SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE;
END IF;
RETURN n_ret;
END;

2) put this expression to item's  formula property
nvl(:SHIP_DET.U_PRICE,0)+f_1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
yes henka
excellent
one small thing only , how to format the output as percentage with 2 decimal place
Information Technology SpecialistCommented:
RETURN (to_char(100 * n_ret, 'fm9990.00')||'%'
Information Technology SpecialistCommented:
Open parenthesis needs to be removed -
RETURN to_char(100 * n_ret, 'fm9990.00')||'%'
Also function needs to return varchar2.
programmer-analystCommented:
Another option is setting format mask on item's format mask property.
Author Commented:
awking
the formula will be like this

``````FUNCTION f_1 RETURN varchar2 IS

n_ret   number;
BEGIN
IF nvl(:SHIP_DET.U_PRICE,0)=0 THEN
n_ret := 0;
ELSE
n_ret:=:SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE;
END IF;
RETURN to_char(100 * n_ret, 'fm9990.00')||'%' ;
END;
``````

but it return caluculation error
henka , what is the suitable format mask ?
programmer-analystCommented:
Format mask is right - fm9990.00. I think that problem is in returning value, I would remove '%':
RETURN to_char(100 * n_ret, 'fm9990.00');
Author Commented:
yes henka now the error handled
but the output is just a number with 2 decimal , not a percentage
programmer-analystCommented:
You can either
1) put  '%' on canvas after an item
or
2) change item's datatype property to CHAR and add '%' (as it has been) to return:
RETURN to_char(100 * n_ret, 'fm9990.00'))||'%' ;
Author Commented:
the other option still gives error in calculation
programmer-analystCommented:
Now I see that item's datatype  must be NUMBER when formula is used. Sorry :(.
So you ought to use 1).
Information Technology SpecialistCommented:
Since the data type is number, the function needs to return number -
return (100 * n_ret, 'fm9990.00') ==> no need for to_char function
Unfortunately, Oracle does not have a built-in format mask for percentages, so it will need to be manipulated to show the percent sign when displayed. Perhaps something like the following might work for the item's formula property:
to_char(nvl(:SHIP_DET.U_PRICE,0)+f_1)||'%'
Author Commented:
yes , now it is ok
thanx awking
thanx henka
now for distributing the points

to be fair , the original question solved by henka , and fairly she is entitled for the complete points
but i added additional question which solved by awking
should i accept this question totally for henka
and open another question for the percentage and accepted for awking ?
or , i could distribute 350 to henka , 150 to awking
please advise
thanx
programmer-analystCommented:
I think that you can distribute points in this question.
Author Commented:
ok , let wait till end of today to know if awking agree with this suggestion
thanx
Information Technology SpecialistCommented:
NiceMan331,
I have no problem with the suggestion made and wouldn't have a problem if you wish to award more points to Henka either.
Author Commented:
you and henka are not only an experts , but polite  too
thanx to you both
programmer-analystCommented:
:)
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.