How to use to_number and nullif in sql-loader?

Question

I've had a similar problem with dates (combination of to_date and nullif) here : How to use decode in sql-loader?

And it was solved nicely.

My problem is that a numeric field in my CSV file can have these formats : 999,999,999.99 or just a dot '.' for null values.

This is working :

MINQUANTITY      "TO_NUMBER(:MINQUANTITY, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS='',.''')"

or

MINQUANTITY      NULLIF MINQUANTITY      = '.'

---

But it is not working when I'm trying to combine both :

MINQUANTITY      "TO_NUMBER(:MINQUANTITY, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS='',.''')  NULLIF :MINQUANTITY= '.'"

Here is the error log :

Record 1: Rejected - Error on table MY_TABLE, column MINQUANTITY.
ORA-00917: missing comma

How can I combine these ?

Solution

Your NULLIF condition should not be inside the double-quotes for the SQL string; and it needs to come first. From the documentation:

• The SQL string appears after any other specifications for a given column.

• The SQL string must be enclosed in double quotation marks.

...

• The SQL string is evaluated after any NULLIF or DEFAULTIF clauses, but before a date mask.

So it should be:

MINQUANTITY NULLIF MINQUANTITY = '.'
  "TO_NUMBER(:MINQUANTITY, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS='',.''')"

(You can split into two lines for readability, both parts still apply to the `MINQUANTITY field).

In the log that's reported as:

MINQUANTITY                          NEXT     *   ,       CHARACTER
    NULL if MINQUANTITY = 0X2e(character '.')
    SQL string for column : "TO_NUMBER(:MINQUANTITY, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS='',.''')"

(Have you got your NLS chars the right way round? This is treating , as the decimal separator, while your question suggests you're using .. Either will work as long as the value is enclosed in double-quotes, of course).

Comments

[kmas]

It works ! Thanks. And for NLS, you seem to be right too. Thanks again.