问题描述
我在这里遇到过类似的日期问题(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 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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!