Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

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
Avatar of Mike McCracken
Mike McCracken

Try this

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

mlmcc
Avatar of NiceMan331

ASKER

Error as per attached
Untitled.png
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
Dear , it is not a select statement , I'm trying to put the formula in property item for the calculated item in the form
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yes henka
excellent
one small thing only , how to format the output as percentage with 2 decimal place
RETURN (to_char(100 * n_ret, 'fm9990.00')||'%'
Open parenthesis needs to be removed -
RETURN to_char(100 * n_ret, 'fm9990.00')||'%'
Also function needs to return varchar2.
Another option is setting format mask on item's format mask property.
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 ?
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');
yes henka now the error handled
but the output is just a number with 2 decimal , not a percentage
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'))||'%' ;
the other option still gives error in calculation
Now I see that item's datatype  must be NUMBER when formula is used. Sorry :(.
So you ought to use 1).
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I think that you can distribute points in this question.
ok , let wait till end of today to know if awking agree with this suggestion
thanx
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.
you and henka are not only an experts , but polite  too
thanx to you both