DECODE different things

[drkestrel]

I am using DECODE with SQL Loader.
My objective is to convert any of the following to NULL for a field called CODE:
Spaces
-111

I tried the following:

CODE    POSITION(249:260)   INTEGER EXTERNAL "DECODE(trim(:CODE),'',null,'-111',null,:CODE)"

Problem is that (from what I guessfrom reading the documentation), DECODE seems to have coverted the value to the type as defined in the first expression- i.e. type for '' is VARCHAR or CHAR, and that the field is numeric....

anyway, the outcome is that -111 is not set to null. Any suggestions for getting around with this?

 

[Nebuchednezzar]

How about...
"DECODE(trim(to_charCODE)),'',null,'-111',null,:CODE)"

 

[drkestrel]

It should have been

CODE POSITION(249:260) INTEGER EXTERNAL "DECODE(trimCODE),'',null,-111,null,:CODE)"

Witout the '', cos field is Numeric!