DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Loader Error

  1. #1
    Join Date
    Jul 2015
    Posts
    3

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Quote Originally Posted by saravanantgu View Post
    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

  3. #3
    Join Date
    Jul 2015
    Posts
    3

    SQL Loader

    Quote Originally Posted by LKBrwn_DBA View Post
    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.

  4. #4
    Join Date
    Jul 2015
    Posts
    3
    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.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Quote Originally Posted by saravanantgu View Post
    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
  •  


Click Here to Expand Forum to Full Width