Hi everyone,
I have the following if-then-else in a trigger called when-validate-item where I'm trying to make it work the way I want, but only runs if I have 3 numeric values for each item. If put a letter in one of them then I get the following error:
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.
Can anyone please help me to fix my logic?
Begin
If (:BLOCK1.ITEM1 BETWEEN 0 AND 100) and (:BLOCK2.ITEM2 BETWEEN 0 AND 100) and (:BLOCK3.ITEM3 BETWEEN 0 AND 100) Then
:BLOCK1.ITEM4 := (:BLOCK1.ITEM1*0.40) + (:BLOCK1.ITEM2*0.40) + (:BLOCK1.ITEM3*0.20);
Elsif
(:BLOCK1.ITEM1 IN ('F', 'P', 'NA')) and (:BLOCK1.ITEM2 BETWEEN 0 AND 100) and (:BLOCK1.ITEM3 BETWEEN 0 AND 100) Then
:BLOCK1.ITEM4 := (:BLOCK1.ITEM2 + :BLOCK1.ITEM3)/2;
Elsif
(:BLOCK1.ITEM2 IN ('F', 'P', 'NA')) and (:BLOCK1.ITEM1 BETWEEN 0 AND 100) and (:BLOCK1.ITEM3 BETWEEN 0 AND 100) Then
:BLOCK1.ITEM4 := (:BLOCK1.ITEM1 + :BLOCK1.ITEM3)/2;
Elsif
(:BLOCK1.ITEM1 IN ('F')) AND :BLOCK1.ITEM2 IN ('F')) AND (:BLOCK1.ITEM3 IN ('F')) Then
:BLOCK1.ITEM4 := 'F';
Elsif
(:BLOCK1.ITEM1 IN ('P')) AND (:BLOCK1.ITEM2 IN ('P')) AND (:BLOCK1.ITEM3 IN ('P')) Then
:BLOCK1.ITEM4 := 'P';
Elsif
(:BLOCK1.ITEM1 IN ('NA')) AND (:BLOCK1.ITEM2 IN ('NA')) AND (:BLOCK1.ITEM3 IN ('NA')) Then
:BLOCK1.ITEM4 := 'NA';
Else
Null;
End if;
End;
Edited by: user626836 on Sep 28, 2011 8:42 AM
Edited by: user626836 on Sep 28, 2011 8:54 AM