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

Open in new window


how do i put this formula in the folmula of that item
NiceMan331Asked:
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.

mlmccCommented:
Try this

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

mlmcc
NiceMan331Author Commented:
Error as per attached
Untitled.png
mlmccCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author 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
Helena Marková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.

Start your 7-day free trial
NiceMan331Author Commented:
yes henka
excellent
one small thing only , how to format the output as percentage with 2 decimal place
awking00Information Technology SpecialistCommented:
RETURN (to_char(100 * n_ret, 'fm9990.00')||'%'
awking00Information Technology SpecialistCommented:
Open parenthesis needs to be removed -
RETURN to_char(100 * n_ret, 'fm9990.00')||'%'
Also function needs to return varchar2.
Helena Markováprogrammer-analystCommented:
Another option is setting format mask on item's format mask property.
NiceMan331Author 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;

Open in new window


but it return caluculation error
henka , what is the suitable format mask ?
Helena Marková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');
NiceMan331Author Commented:
yes henka now the error handled
but the output is just a number with 2 decimal , not a percentage
Helena Marková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'))||'%' ;
NiceMan331Author Commented:
the other option still gives error in calculation
Helena Markováprogrammer-analystCommented:
Now I see that item's datatype  must be NUMBER when formula is used. Sorry :(.
So you ought to use 1).
awking00Information 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)||'%'
NiceMan331Author 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
Helena Markováprogrammer-analystCommented:
I think that you can distribute points in this question.
NiceMan331Author Commented:
ok , let wait till end of today to know if awking agree with this suggestion
thanx
awking00Information 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.
NiceMan331Author Commented:
you and henka are not only an experts , but polite  too
thanx to you both
Helena Marková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.