以下是我的sqlload的控制文件头
LOAD DATA
INFILE *
APPEND
INTO TABLE test_slqldr
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ID,LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8,LEVEL9,UNIT,CODE,CODE_TYPE,FETCH_SQL)
BEGINDATA
0001,我的e家收入,,,,,,,,,元,EJSR000000,1, select latn_id,sum(nvl(chrg,0))
from (select * from tb_b_ft_ofr_rev_tmp_#MONTH_NO# where translate(chrg_level, '\\0123456789', '\\') is null) a , tb_b_dim_urban_rural b
where a.ofr_type = 'e6'
and a.urban_rural_id = b.urban_rural_id and b.urban_level2_name = '城市'
and ((to_number(a.chrg_level) < 36 and a.chrg_level not like '%年付%') or
(to_number(substr(a.chrg_level, length(a.chrg_level) - 2)) < 36 and
a.chrg_level like '%年付%'))
and a.acct_item_type_id in (select b.acct_item_type_id
from TB_B_CW_AUTO_E6E8_ACCT_ITEM a
,tb_b_cw_auto_e6e8_acct_item_rf b
where a.id = b.id and a.level1='增值及综合信息应用服务收入')
group by latn_id (一条记录结束)一般情况下 TRAILING NULLCOLS 用来指示 一条记录的结束是 个回车符,但是我的一条记录的结束 是一条sql语句,并且sql语句
中有回车符,我 想TRAILING 能不能通过这个来限制 告诉oracle 我的一条记录的结束是 latn_id ,那我把他写成
TRAILING latn_id ,但是oracle却报错,请问有没有这种用法,应该怎么写
LOAD DATA
INFILE *
APPEND
INTO TABLE test_slqldr
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ID,LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8,LEVEL9,UNIT,CODE,CODE_TYPE,FETCH_SQL)
BEGINDATA
0001,我的e家收入,,,,,,,,,元,EJSR000000,1, select latn_id,sum(nvl(chrg,0))
from (select * from tb_b_ft_ofr_rev_tmp_#MONTH_NO# where translate(chrg_level, '\\0123456789', '\\') is null) a , tb_b_dim_urban_rural b
where a.ofr_type = 'e6'
and a.urban_rural_id = b.urban_rural_id and b.urban_level2_name = '城市'
and ((to_number(a.chrg_level) < 36 and a.chrg_level not like '%年付%') or
(to_number(substr(a.chrg_level, length(a.chrg_level) - 2)) < 36 and
a.chrg_level like '%年付%'))
and a.acct_item_type_id in (select b.acct_item_type_id
from TB_B_CW_AUTO_E6E8_ACCT_ITEM a
,tb_b_cw_auto_e6e8_acct_item_rf b
where a.id = b.id and a.level1='增值及综合信息应用服务收入')
group by latn_id (一条记录结束)一般情况下 TRAILING NULLCOLS 用来指示 一条记录的结束是 个回车符,但是我的一条记录的结束 是一条sql语句,并且sql语句
中有回车符,我 想TRAILING 能不能通过这个来限制 告诉oracle 我的一条记录的结束是 latn_id ,那我把他写成
TRAILING latn_id ,但是oracle却报错,请问有没有这种用法,应该怎么写
continueif参数就可以了。
是这样的吗
LOAD DATA
INFILE *
APPEND
INTO TABLE test_slqldr
FIELDS TERMINATED BY ","
TRAILING continueif (ID,LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8,LEVEL9,UNIT,CODE,CODE_TYPE,FETCH_SQL)
BEGINDATA 还是 continueif 是个函数