-
SQL Loader Error
Hi,
I am trying to load my data into two tables based on the first position value , which is not in data base. So, if the first value is "0" I have to insert in table1 , if the first value is "1" then I have to insert in table2. I used when condition to do it. Below is my sample code I used :
load data
infile 'C:\Sprint\CEAS\Sample.txt'
into table CLONE_GEOP_SITE
WHEN(1) = '0'
fields terminated by "|" optionally enclosed by '/'
TRAILING NULLCOLS (Columns)
WHEN(1) = '1'
fields terminated by "|" optionally enclosed by '/'
TRAILING NULLCOLS (Columns)
Getting below error :
value used for ROWS parameter changed from 64 to 32
Record 1: Discarded - failed all WHEN clauses.
1 Row not loaded because all WHEN clauses were failed.
-
Originally Posted by saravanantgu
Hi,
I am trying to load my data . . .
. . . all WHEN clauses were failed.
Try:
Code:
. . .
WHEN POSITION(1:1) = '0'
. . .
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
SQL Loader
Originally Posted by LKBrwn_DBA
Try:
Code:
. . .
WHEN POSITION(1:1) = '0'
. . .
Thanks it works... I have one more issue. I have a data file and the first column is not in data base - but it drives in which table the specific record should go. I used Record_Type Filler POSITION(1) CHAR - its working.
Now I have a filler column in the middle which should have just null and that needs to be ignored (no need to store that in database). That column is NOT defined in specified position.
Below is the example :
|ABW||BR|BR|BR|1996/04/03|1988/09/15|C|A|ARUBA||||*|
|AC||BR||BR|1988/09/15|1988/09/15|A|A|ASCENSION ISLAND||||*|
|ABW||BR
|AC||BR ---> I no need to store this null, which is in between ||. Please provide your valuable suggestions.
-
Thanks it works. Now I have issue with Filler. I have a first column which needs to be skipped (no need to insert in database). So I defined as below : Record_Type Filler POSITION(1) CHAR and it works... Now I have filler column in the middle which is not in specified position. See below :
|ABW||BR|BR|BR|1996/04/03|1988/09/15|C|A|ARUBA||||*|
|AC||BR||BR|1988/09/15|1988/09/15|A|A|ASCENSION ISLAND||||*|
|ABW||BR
|AC||BR --> I need to avoid the null in between ||. Please provide your valuable suggestions.
-
Originally Posted by saravanantgu
Thanks it works. Now I have issue with Filler. . . column in the middle which is not in specified position.
Insert the filler between the fields in the input:
Code:
Before_fld,
ignore FILLER,
after_fld,
. . .
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|