问题描述
下面是我的原始文件
br-Name-acc-Bal
10 B LO 1,000.12-
10 C SB 2,000.15
11 D FD 5,000.00
12 E RD 6,000.00
14 G LO 8,000.56-
15 Q LO 7,000.89-
我想使用 sqlldr 将这些数据导入到我的表中,并且我需要在 bal 列前使用 (-) 符号而不使用 (,) 逗号.
I want to import these data into my table using sqlldr and I need (-) symbol front of the bal column without (,) comma.
load data
infile *
truncate into table table1
fields
trailing nullcols
(
Br POSITION(1:2) ,
Name POSITION(4:5),
acc POSITION(6:7) ,
bal POSITION(10:18) ----->What should I mention here??? (Here am using datatype as number(17,3))
)
begindata
我正在寻找如下结果
I am looking for the result like below
你能帮我解决这个问题吗
Could you please help me on this
推荐答案
对于您发布的数据,它看起来像这样:取 BAL
值的子串,长度最多为 8 个字符,然后将其相乘按 -1
如果第 9 个字符是 -
.
For data you posted, it would look like this: take substring of the BAL
value up to 8 characters in length and multiply it by -1
if the 9th character is -
.
目标表:
SQL> desc table1
Name Null? Type
----------------------------------------------------- -------- ---------------------
BR VARCHAR2(10)
NAME VARCHAR2(10)
ACC VARCHAR2(10)
BAL NUMBER
控制文件:
load data
infile *
truncate into table table1
fields
trailing nullcols
(
Br POSITION(1:2),
Name POSITION(4:5),
acc POSITION(6:7),
bal POSITION(10:18) "to_number(substr(:bal, 1, 8), '9g999d99', 'nls_numeric_characters = .,') *
case when substr(:bal, -1) = '-' then -1 else 1 end"
)
begindata
10 B LO 1,000.12-
10 C SB 2,000.15
11 D FD 5,000.00
12 E RD 6,000.00
14 G LO 8,000.56-
15 Q LO 7,000.89-
加载会话&结果:
Loading session & result:
SQL> $sqlldr scott/tiger@kc11gt control=test35.ctl log=test35.log
SQL*Loader: Release 11.2.0.1.0 - Production on Sri O×u 24 12:24:59 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 5
Commit point reached - logical record count 6
SQL> select * from table1;
BR NAME ACC BAL
---------- ---------- ---------- ----------
10 B LO -1000,12
10 C SB 2000,15
11 D FD 5000
12 E RD 6000
14 G LO -8000,56
15 Q LO -7000,89
6 rows selected.
SQL>
没有显示 5000 和 6000 的小数,因为它是 NUMBER
数据类型列;如果您想查看小数,请使用带有适当格式掩码的 TO_CHAR
函数或(如果您使用的工具允许)设置数字格式,例如在 SQL*Plus 中
There are no decimals displayed for 5000 and 6000 because it is the NUMBER
datatype column; if you want to see decimals, either use TO_CHAR
function with appropriate format mask or (if tool you use allows it) set numeric format, such as in SQL*Plus
SQL> set numformat 9999d99
SQL> select * from table1;
BR NAME ACC BAL
---------- ---------- ---------- --------
10 B LO -1000,12
10 C SB 2000,15
11 D FD 5000,00
12 E RD 6000,00
14 G LO -8000,56
15 Q LO -7000,89
这篇关于Sqlldr loader 编号列导入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!