| Values autoupdate / ORA-01476 (merged) [message #238282] |
Thu, 17 May 2007 05:23  |
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
|
Senior Member |
|
|
we can see a big example MS Excel
i-e we are using a excel sheet.
column
b1 = 2
c1 = 2
d1 = 1 and e1 has formula
e1 = "=+D1+B1*C1/100" -- result 1.08
now we want percentage
b2 = 40
c2 = 30
e2 = "=+B2*E1/100" result 0.43
e3 = "=+D1+B1*C1/100" result 0.13
when we change b1 value with 3, all the values automatic changes without changing in b2 and c2 column.
I wrote these code in oracle when-valid-item trigger when i change item b1 value with 3 nothing changes in form because i wrote code in d1 trigger when-validate-item.
In this situation I must put any value in d1 item then it will changes the current total and remain formula will not change.
now I want that when i put value 3 in b1 item all the values automatically change according to input. (like in excel)
|
|
|
|
|
|
|
|
| FRM-40735 ORA-01476 [message #238986 is a reply to message #238282] |
Mon, 21 May 2007 02:16  |
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
|
Senior Member |
|
|
I wrote this line in formula
(:stot1+ :bottomcost.cmt1+ :bottomcost.cf1+ :bottomcost.oh1+ :bottomcost.ins1+ :bottomcost.naf1 + :bottomcost.com1) / nvl(:exc_rate,0)
and there is no 0 Value but when I execute query then return this error
FRM-40735
ORA-01476
I know never can any thing divide by 0, there is any possbility that when ever it found 0 in :exc_rate then don't show error and show the proper record becuse with above formula when I execute query in this formula's block record is not showing and shows the said error.
|
|
|
|
| Re: FRM-40735 ORA-01476 [message #239044 is a reply to message #238986] |
Mon, 21 May 2007 05:55 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As it is evaluation of your previous question, please, continue discussion in existing topic - do not open a new one.
NVL function will not help here; you are not dealing with absence of a value (i.e. NULL), but zero. In that case, you'd use DECODE or CASE statement. As it is a formula column, you can't use DECODE (actually, you *may*, but I don't know the way to do that), but CASE will help.
Formula column should look like this: assume that you have simple
Now deal with zero divide like this: 1E99 is here just as a very large number; depending on your "a" and "b" values, this might be too large, or even not large enough. However, I hope you'll get the point.case
when :b = 0 then :a / 1E99
else :a / :b
end
I hope I didn't misunderstood your question!
|
|
|
|
| Re: FRM-40735 ORA-01476 [message #239054 is a reply to message #239044] |
Mon, 21 May 2007 06:22 |
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
|
Senior Member |
|
|
Thank you Dear You gave my hints.
I write this line in When-Validate-Item
if :exc_rate is null or :exc_rate = 0 then
return;
end if;
Now Work accordingly and there is no any error.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
