oracle 如何在 sql-loader 中使用解码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18130689/
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 decode in sql-loader?
提问by kmas
I use sqlldr to import CSV files and I have some problem with date multiple formats.
我使用 sqlldr 导入 CSV 文件，但我在日期多种格式方面遇到了一些问题。
Dates inside the CSV file are DD/MM/YYYYand if there is no date it is a single dot
CSV 文件中的日期为DD/MM/YYYY，如果没有日期，则为单点
CSV file
CSV 文件
DATE_COLUMN;OTHER_COLUMN
01/01/2013;other column content 1
.;other column content 2
My .ctl file for sqlldr
我的 .ctl 文件用于 sqlldr
LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
COLUMNDATE "decode(:COLUMNDATE ,NULL,'.', to_date(:COLUMNDATE ,'DD/MM/YYYY'))",
OTHER_COLUMN
)
The import is working when I use :
当我使用时，导入工作正常：
decode(:COLUMNDATE ,NULL,'.'))
or
或者
to_date(:COLUMNDATE ,'DD/MM/YYYY')
But not when I try to combine both...
但是当我尝试将两者结合起来时...
Here is the error log :
这是错误日志：
Record 1: Rejected - Error on table table_to_fill, column COLUMNDATE.
ORA-01858: a non-numeric character was found where a numeric was expected
How can I combine these, please ?
请问如何将这些结合起来？
I thought that the last parameter of the "decode" function was for the default value of the column, am I wrong ?
我以为“解码”函数的最后一个参数是列的默认值，我错了吗？
回答by Ed Gibbs
SQL Loader's "regular" syntax should be enough here. Try this:
SQL Loader 的“常规”语法在这里应该足够了。尝试这个：
LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
COLUMNDATE DATE(7) "DD/MM/YYYY" NULLIF COLUMNDATE = "."
OTHER_COLUMN
)
