Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    12

    Exclamation Unanswered: Where clause in control file

    Hi All,

    This is for SQL Loader experts.

    Is it possible to use where clause to discard one of the item which is not able to fit into column because of the length constraint.

    Example:
    1. Remove first digit from the item_ID where ITEM_ID IN (12345)
    2. Do not load data WHERE ITEM_ID IN (12345)

    An example control file would be really appreciated.

    Thanks!!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You don't have to do anything about it - those values won't be loaded anyway due to data errors.

    If you still want to load them (according to the first request, removing the first digit), you could do that: this is a sample table
    Code:
    SQL> desc test
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     NUM_VAL                                NUMBER(4)
     SOME_NAME                              VARCHAR2(20)
    
    SQL>
    Control file and the result:
    Code:
    load data
    infile *
    replace
    into table test
    fields terminated by ","
    ( 
      num_val "decode(length(:num_val), 4, :num_val, substr(:num_val, 2, length(:num_val)))",
      some_name
    )
    
    begindata
    8865,Little
    12345,Foot
    987,Heyit
    Code:
    SQL> select * from test;
    
       NUM_VAL SOME_NAME
    ---------- --------------------
          8865 Little
          2345 Foot
            87 Heyit
    If you don't want to load records according to a certain value, you'd use WHEN clause:
    Code:
    load data
    infile *
    replace
    
    into table test
    when (num_val <> '12345')
    fields terminated by ","
    ( 
      num_val,
      some_name
    )
    
    begindata
    8865,Little
    12345,Foot
    987,Heyit
    Code:
    SQL> select * from test;
    
       NUM_VAL SOME_NAME
    ---------- --------------------
          8865 Little
           987 Heyit
    
    SQL>
    It would be useful if you take some time and read the SQL*Loader documentation.

  3. #3
    Join Date
    May 2010
    Posts
    12

    SQL Loader with where clause

    Thank You Littlefoot.

    In your example:

    Data Before Triming
    -------------------
    8865,Little
    12345,Foot
    987,Heyit


    You have suggested to use:

    (
    num_val "decode(length(:num_val), 4, :num_val, substr(:num_val, 2, length(:num_val)))", some_name
    )

    and then output would be


    Data After Triming
    ------------------
    8865 Little
    2345 Foot
    87 Heyit

    It shows triming 2nd and 3rd line. Is it correct?

    Also can we change this syntax to a particular valus row, example:
    Trim num_val from 5 digit to 4 digit (remove first digit only) where item_no = 1234.

    Thanks in advance!!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I apologize - my bad ... I don't know what was I thinking (nothing smart, obviously).

    Yes, you are right - I trimmed all values whose length was different from 4. The correct line (or, perhaps better the whole control file) would be
    Code:
    load data
    infile *
    replace
    into table test
    fields terminated by ","
    ( 
      num_val "decode(length(:num_val), 5, substr(:num_val, 2, length(:num_val)), :num_val)",
      some_name
    )
    
    begindata
    8865,Little
    12345,Foot
    987,Heyit
    Code:
    SQL> select * from test;
    
       NUM_VAL SOME_NAME
    ---------- --------------------
          8865 Little
          2345 Foot
           987 Heyit
    So, if length of the input number is 5, then trim the first digit. Otherwise, load the whole number.

    If there were different lengths (for example, not only 5 but 6, 7 or more), you'd have to substitute decoding LENGTH with decoding SIGN(LENGTH - 4). Something like this:
    Code:
    load data
    infile *
    replace
    into table test
    fields terminated by ","
    ( 
      num_val "decode(sign(length(:num_val) - 4), 1, substr(:num_val, -4), :num_val)",
      some_name
    )
    
    begindata
    8865,Little
    12345,Foot
    987,Heyit
    87654321,Long Num
    Code:
    SQL> select * from test;
    
       NUM_VAL SOME_NAME
    ---------- --------------------
          8865 Little
          2345 Foot
           987 Heyit
          4321 Long Num
    
    SQL>

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •