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 :
how do i put this formula in the folmula of that item
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
ASKER
Error as per attached
Untitled.png
Untitled.png
Did you try to run that statement or did you put it into your SELECT?
SELECT
CASE WHEN
nullif(:SHIP_DET.COST_PRIC E, 0) = 0 THEN 0
ELSE :SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE
END
FROM Dual
mlmcc
SELECT
CASE WHEN
nullif(:SHIP_DET.COST_PRIC
ELSE :SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE
END
FROM Dual
mlmcc
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes henka
excellent
one small thing only , how to format the output as percentage with 2 decimal place
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.
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.
ASKER
awking
the formula will be like this
but it return caluculation error
henka , what is the suitable format mask ?
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 ?
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');
RETURN to_char(100 * n_ret, 'fm9990.00');
ASKER
yes henka now the error handled
but the output is just a number with 2 decimal , not a percentage
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'))||'%' ;
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'))||'%' ;
ASKER
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).
So you ought to use 1).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
ASKER
ok , let wait till end of today to know if awking agree with this suggestion
thanx
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.
I have no problem with the suggestion made and wouldn't have a problem if you wish to award more points to Henka either.
ASKER
you and henka are not only an experts , but polite too
thanx to you both
thanx to you both
:)
CASE WHEN
nullif(:SHIP_DET.COST_PRIC
ELSE :SHIP_DET.U_PRICE / :SHIP_DET.COST_PRICE END
mlmcc