SQL & PL/SQL

Before anyone starts ranting :-} I know it is bad design to put numbers into a varchar, however as in most development situations, I am a victim of the database designer that didnt know what he was doing (or didnt care) and I have to live with it.

So onto my problem, I have a solution, but I dont understand why it is doing what it is doing.

I am creating views of two tables containing bank account information. In one table there is an Account Number (numeric in a number field) and in both tables there is the Application Number which is unfortunately a number in one table and a varchar2 in the other. I need to be able to JOIN based on the Application Number.

As the underlying table is multi-national, we access the data through views that restrict our data to what we need to see, i.e. UK Data only. Within the view, we convert the varchar2 Application Number to a Number, using TO_NUMBER(). This worked when compiling the view, but on doing a SELECT * from view we get an Invalid Number Error.

It turns out that for some reason there is a space in the middle of one of the application numbers, causing the conversion to fail and hence the invalid number error. In browsing the internet for a solution I have used a combination of decode, translate and replace to remove the eroneous records, but couldnt use the same code to identify my problem record.

The code reads

DECODE( (REPLACE( TRANSLATE( TRIM(ApplicationID), '0123456789','00000000000'),'0',NULL)), NULL, TO_NUMBER(trim(ApplicationID))) AS ApplicationID,

this works in the View, I get my data, and the record with invalid data ends up with a null Application Number. Running this in a where clause, with a slight adjustment, such as ...

WHERE DECODE( (REPLACE( TRANSLATE( TRIM(ApplicationID), '0123456789','00000000000'),'0',NULL)), NULL, 'NUMBER') != 'NUMBER'

returns no records. This is what I dont understand. Running ....

WHERE LENGTH(REPLACE(TRANSLATE(NUM_APPL,'0123456789','0000000000'),'0',NULL)) >0

identifies my problem record.

If the first DECODE works in the view, why does this not help me identify my problem record ???

In the great scheme of things it isnt critical, but I do like to understand when I encounter this sort of problem, as it may help me resolve the problem in a better way (no, I cant change the table field to a number !!!), or it may just help me in the future

Ohh, if its important, we use Oracle 9i and I am typically using either TOAD or SQLPlus to access.

Thanks in advance to anyone that wants to take an interest !!!!!

Craig