写了个ctl如下:
LOAD DATA
APPEND
INTO TABLE %TABLENAME%
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
(
SERV_TYPE,
FILE_MODE,
DL_TYPE,
FILE_NAME,
ALL_SHEET_CNT,
SHEET_CNT,
ERR_CNT,
BASIC_FEE,
INFO_FEE,
DISC_FEE,
FLOW,
DURATION,
REMARK
BILL_MONTH CONSTANT "%FILENAME%"
PROV_CODE CONSTANT "%FILENAME%"
)
%FILENAME%是外部传进来的值,例如B_YYYYMMNNN.ZZZ 问题是 BILL_MONTH我只想取YYYYMM, PROV_CODE我想取NNN,在CTL里面有没有办法可以对%FILENAME%使用substr这样类似的函数?
LOAD DATA
APPEND
INTO TABLE %TABLENAME%
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
(
SERV_TYPE,
FILE_MODE,
DL_TYPE,
FILE_NAME,
ALL_SHEET_CNT,
SHEET_CNT,
ERR_CNT,
BASIC_FEE,
INFO_FEE,
DISC_FEE,
FLOW,
DURATION,
REMARK
BILL_MONTH CONSTANT "%FILENAME%"
PROV_CODE CONSTANT "%FILENAME%"
)
%FILENAME%是外部传进来的值,例如B_YYYYMMNNN.ZZZ 问题是 BILL_MONTH我只想取YYYYMM, PROV_CODE我想取NNN,在CTL里面有没有办法可以对%FILENAME%使用substr这样类似的函数?
没实验,不知是否好用
-- 示例
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16), // 这个字段的开始位置在前一字段的结束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BILL_MONTH CONSTANT SUBSTR(%FILENAME%, 3, 6)