Home » RDBMS Server » Server Utilities » SQL Loader WHEN clause checking for NULL/BLANK
| SQL Loader WHEN clause checking for NULL/BLANK [message #73234] |
Fri, 05 March 2004 08:12  |
Aldo Valerio
Messages: 7
Registered: March 2004
|
Junior Member |
|
|
Oracle 8.1.6 on WIndows 2000.
I'm trying to load 2 different tab-delimited layouts into the same table, but I will never know in advance which of the 2 formats I'm processing. I have very little control over the input since it's created by a SAP download which generates 256 columns in total, with the unused ones being empty. Both layouts have the 1st col. as NULL. Layout 1 has cols. 2-19 always with values, and no other cols. Layout 2 has cols. 2-14 always with values, cols. 15-24 sometimes with values, cols. 25-26 always with values, and cols. 27-29 sometimes with values. I'm trying to use two INTO TABLE statements, each with their own WHEN clause to recognize which type of record is processed.
My control file:
OPTIONS (SKIP=2) -- Skip first 2 records (column titles)
LOAD DATA
INFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.DAT'
BADFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.BAD'
DISCARDFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.DSC'
REPLACE
INTO TABLE sradb.sap_extract -- (29 columns)
WHEN CFT_ID <> BLANKS
AND CFT_DESC <> BLANKS
FIELDS TERMINATED BY X'09'
( COLUMN_1 FILLER CHAR,
COST_CENTER_CODE CHAR(4),
ALT_BOM_CODE FILLER CHAR,
ALT_BOM_DESC FILLER CHAR,
SKU_ID INTEGER EXTERNAL "TO_NUMBER(RTRIM(LTRIM(:SKU_ID)))",
FAMILY_CODE CHAR(2),
FAMILY_DESC FILLER CHAR,
BRAND_CODE CHAR(4) "RTRIM(LTRIM(:BRAND_CODE))",
BRAND_DESC FILLER CHAR,
PACK_TYPE_CODE CHAR(3),
PACK_STYLE_CODE FILLER CHAR,
CIG_LENGTH CHAR(9) "RTRIM(LTRIM(:CIG_LENGTH))",
SAP_PRIME_MARKET_CODE CHAR(2),
MARKET_TYPE_CODE CHAR(1),
SKU_ID_2 FILLER CHAR,
FAMILY_CODE_2 FILLER CHAR,
FAMILY_DESC_2 FILLER CHAR,
BRAND_CODE_2 FILLER CHAR,
BRAND_DESC_2 FILLER CHAR,
PACK_TYPE_CODE_2 FILLER CHAR,
PACK_STYLE_CODE_2 FILLER CHAR,
CIG_LENGTH_2 FILLER CHAR,
SAP_PRIME_MARKET_CODE_2 FILLER CHAR,
MARKET_TYPE_CODE_2 FILLER CHAR,
CFT_ID INTEGER EXTERNAL "TO_NUMBER(RTRIM(LTRIM(:CFT_ID)))",
CFT_DESC FILLER CHAR,
REVISION_CODE CHAR(1),
EFFECTIVE_DATE DATE "DD.MM.YYYY" "RTRIM(LTRIM(:EFFECTIVE_DATE))",
BLEND_CODE CHAR(15) "RTRIM(LTRIM(:BLEND_CODE))"
)
INTO TABLE sradb.sap_extract -- (19 columns)
WHEN PACK_TYPE_CODE_2 = BLANKS
AND PACK_STYLE_CODE_2 = BLANKS
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
( COLUMN_1 FILLER CHAR,
COST_CENTER_CODE CHAR(4),
ALT_BOM_CODE FILLER CHAR,
ALT_BOM_DESC FILLER CHAR,
SKU_ID INTEGER EXTERNAL "TO_NUMBER(RTRIM(LTRIM(:SKU_ID)))",
FAMILY_CODE CHAR(2),
FAMILY_DESC FILLER CHAR,
BRAND_CODE CHAR(4) "RTRIM(LTRIM(:BRAND_CODE))",
BRAND_DESC FILLER CHAR,
PACK_TYPE_CODE CHAR(3),
PACK_STYLE_CODE FILLER CHAR,
CIG_LENGTH CHAR(9) "RTRIM(LTRIM(:CIG_LENGTH))",
SAP_PRIME_MARKET_CODE CHAR(2),
MARKET_TYPE_CODE CHAR(1),
CFT_ID INTEGER EXTERNAL "TO_NUMBER(RTRIM(LTRIM(:CFT_ID)))",
CFT_DESC FILLER CHAR,
REVISION_CODE CHAR(1),
EFFECTIVE_DATE DATE "DD.MM.YYYY" "RTRIM(LTRIM(:EFFECTIVE_DATE))",
BLEND_CODE CHAR(15) "RTRIM(LTRIM(:BLEND_CODE))",
SKU_ID_2 FILLER CHAR,
FAMILY_CODE_2 FILLER CHAR,
FAMILY_DESC_2 FILLER CHAR,
BRAND_CODE_2 FILLER CHAR,
BRAND_DESC_2 FILLER CHAR,
PACK_TYPE_CODE_2 FILLER CHAR,
PACK_STYLE_CODE_2 FILLER CHAR,
CIG_LENGTH_2 FILLER CHAR,
SAP_PRIME_MARKET_CODE_2 FILLER CHAR,
MARKET_TYPE_CODE_2 FILLER CHAR
)
This works for the larger record (29 cols.), checking that certains cols. are not blank, but not for the smaller record (19 cols.) checking that certain cols. are blank. I've read that SQL Loader consider NULLs to be the same as BLANK, and have tested with = X'0', = '', but all give the same results: all rows are rejected by the when clause. Is it possibel to check that a column is NULL or BLANK? Any other ideas would be much appreciated.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 05:16:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
