oracle 如何在 sql-loader 中使用 to_number 和 nullif？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18143888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use to_number and nullif in sql-loader?
提问by kmas
I've had a similar problem with dates (combination of to_date and nullif) here : How to use decode in sql-loader?
我在这里遇到了类似的日期问题（to_date 和 nullif 的组合）：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.
我的问题是我的 CSV 文件中的数字字段可以具有以下格式：999,999,999.99 或只是一个点 '.' 对于空值。
This is working:
这是工作：
MINQUANTITY "TO_NUMBER(:MINQUANTITY, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS='',.''')"
or
或者
MINQUANTITY NULLIF MINQUANTITY = '.'
But it is not workingwhen 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 ?
我怎样才能结合这些？
采纳答案by Alex Poole
Your NULLIFcondition should not be inside the double-quotes for the SQL string; and it needs to come first. From the documentation:
您的NULLIF条件不应在 SQL 字符串的双引号内；它需要首先出现。从文档：
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.
SQL 字符串出现在给定列的任何其他规范之后。
SQL 字符串必须用双引号括起来。
...
- SQL 字符串在任何 NULLIF 或 DEFAULTIF 子句之后，但在日期掩码之前计算。
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).
（为了便于阅读，您可以分成两行，这两部分仍然适用于`MINQUANTITY 字段）。
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).
（您的 NLS 字符是否正确？这被,视为小数点分隔符，而您的问题表明您正在使用.。当然，只要该值用双引号括起来，两者都可以工作）。
