FZRMILE

The program we use to be able to report on mileage for finance was aborting. It turned out the field were we store the miles someone was reimbursed for is a text field and in some cases people enter text into the field when they are reporting on mileage. There was no logic in the program to test so see if the field was numeric before performing calculations on it.
I found a decode statement that allows you to put logic in-line in the sequel statement to do the check and convert the text to a zero which worked in this case.
decode(replace(translate(fobtext_text,'0123456789.','00000000000'),'0', null), null, to_number(fobtext_text), 0)
The code basically changes all numbers and decimal to a zero, then it replaces all zeros with null, then it tests the string and if it is null then the string was a number and it uses the string as a number otherwise it uses zero.