Sql loader problem: how to detect empty/null value ??
729223Apr 23 2011 — edited Apr 27 2011Hi guys,
I need to make a ctl file in which i may be able to load in multiple tables based on my first column of data in my .csv file. If the first column is emty or null then the data should be loaded in Table A else if it has some value then it should load in Table B.
I have tried several approaches but i am unable to load data if the first column is null
My data values in csv are as follows:
,1,39th,Regular
,1,40th,Regular
,1,41th,Regular
And my current .ctl looks like follows:
LOAD DATA INFILE 'DUMMY.csv'
REPLACE
INTO A
WHEN Column1 =BLANKS AND Column2 !=BLANKS AND Column3!=BLANKS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
Column1 ,
Column2 ,
Column3 ,
---
---
---
)
INTO TABLE B
WHEN Column1 !=BLANKS AND Column2 =BLANKS AND Column3=BLANKS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
Column1 ,
Column2 ,
Column3 ,
---
---
---
)
The log file shows the following if the column1 is empty:
Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
---
---
The problem is that it always fails all condition if the column1 is empty but if column1 is not empty then it successfully loads data.
Any help in this regard will be highly appreciated.