如何在sql-loader中使用to_number和nullif? [英] How to use to_number and nullif in sql-loader?

问题描述

我在这里遇到过类似的日期问题(to_date和nullif的组合):

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

它很好地解决了.

我的问题是我的CSV文件中的数字字段可以具有以下格式:999,999,999.99或仅是点.".表示空值.

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.

这是有效的:

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

或

MINQUANTITY      NULLIF MINQUANTITY      = '.'

---

但是当我试图将两者结合在一起时，它不起作用:

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

这是错误日志:

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

如何将它们结合起来?

推荐答案

您的NULLIF条件不应在SQL字符串的双引号内；它需要首先.来自文档:

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

• SQL字符串出现在给定列的任何其他规范之后.

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

SQL字符串必须用双引号引起来.

The SQL string must be enclosed in double quotation marks.

...

• SQL字符串在任何NULLIF或DEFAULTIF子句之后但在日期掩码之前进行求值.

应该是:

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

(为了便于阅读，您可以分为两行，这两部分仍然适用于"MINQUANTITY"字段).

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

在报告为的日志中:

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

(您是否正确地获取了NLS字符?这是将,用作小数点分隔符，而您的问题表明您正在使用..只要将值括在double中，这两种方法都可以使用-引号，当然).

(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).

这篇关于如何在sql-loader中使用to_number和nullif?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！