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

Thread: SQLLDR help required

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Is it possible to use SQLLoader to transform data from one field, based on a rule, then insert into another column.

    For example, I have a column called Mortgage_Balance and a column called Mortgage_Class.

    If the data in the input file for Mortgage_Balance is between 0 and 9999, then I want to populate Mortgage_Balance with the raw data AND Mortgage_Class with the value 'A'.

    If the data in the input file for Mortgage_Balance is between 10000 and 19999, then I want to populate Mortgage_Balance with the raw data AND Mortgage_Class with the value 'B'. etc.......

    I haven't got a Scooby Do how to do this..........

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    You could write an insert tigger on the table. When a row is inserted into the table (via SQLLDR or any other insert method) the trigger will fire and look at the mortgage_balance column. Depending on the value being inserted it can update the morgage_class field accordingly.

    Cheers
    Moff.

  3. #3
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I think you can do this by using the WHEN-clause in the sqlldr control file.
    Check the docs for more details.

    Hope this helps
    Gert

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    WHEN clause is used to test the condition "in the record." It won't be useful to load data into another column

    If the WHEN clause is TRUE then only record will get loaded into table else it will go to discard file...


    Sameer


  5. #5
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    update script?


    Why not use an update script after you load the data?

    update mtg_table
    set Mortgage_Class = 'A'
    where Mortgage_Balance between 0 and 9999;

    update mtg_table
    set Mortgage_Class = 'B'
    where Mortgage_Balance between 10000 and 19999;

    it would also be easy to write a Proc that you could call to do this whenever you wanted and just have IF/elsif statement in there to do all your edits/changes.

    not sure that you can do this INSIDE Sqlloader

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks for the replies guys. Much appreciated, however...
    Sameer, I think you're correct. The WHEN clause will only act on the tested data, and will not populate another column.
    Moff, the insert trigger sounds interesting, but the table is huge, and i believe that the trigger will significantly slow the load down.
    Jgmagnus, I could run an update script after the load, but this will generate a great deal of logging and rollback. Plus, I think I will potentially chain the rows by doing this.

    Anybody else got any other suggestions specific to SQLLDR ?


  7. #7
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Using the WHEN-clause, you could define multiple layouts depending on the WHEN-condition, so you can also define a different CONSTANT in your separate WHEN-clauses.

    You get something like this in your control-file

    LOAD DATA
    INFILE 'MyInFile' BADFILE 'MyBadFile'
    APPEND
    INTO TABLE Mytable
    WHEN Mortgage_Balance >0 and Mortgage_Balance < 10000
    (
    Mortgage_Balance POSITION(1-10) NUMBER ,
    Mortgage_Class CONSTANT 'A'
    )
    INTO TABLE Mytable
    WHEN Mortgage_Balance > 9999 AND Mortgage_Balance < 20000
    (
    Mortgage_Balance POSITION(1-10) NUMBER ,
    Mortgage_Class CONSTANT 'B'
    )

    OR maybe better ?
    You could use the EXPRESSION clause. Here's an extract from the docs :

    Setting a Column to an Expression Value
    Use the EXPRESSION parameter after a column name to set that column to the value returned by a SQL operator or specially written PL/SQL function. The operator or function is indicated in a SQL string that follows the EXPRESSION parameter. Any arbitrary expression may be used in this context provided that any parameters required for the operator or function are correctly specified and that the result returned by the operator or function is compatible with the datatype of the column being loaded.

    EXPRESSION Parameter
    The combination of column name, EXPRESSION parameter, and a SQL string is a complete field specification.

    column_name EXPRESSION "SQL string"

    Hope this helps
    Gert

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It can easily be done without triggers, only by using SQL*Loader. You must use something that is often called "SQL strings" in SQL*Loader - the functions that can be applied on the source data while it is loaded into table. Here is a simple demonstration (NOTE - if your release does not support CASE expression you can use DECODE nstead):
    Code:
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.1.1 - Production
    
    SQL> show user
    USER is "SCOTT"
    SQL> create table mortage (mortage_balance number, mortage_class varchar2(1));
    
    Table created.
    
    SQL> REM Folowing is the contents of the sample control file:
    SQL> 
      1  LOAD DATA
      2  INFILE *
      3  INTO TABLE mortage
      4  FIELDS TERMINATED BY WHITESPACE
      5  (mortage_balance CHAR,
      6   mortage_class   POSITION (1:1) CHAR
      7     "CASE WHEN :mortage_balance <= 9999 THEN 'A'
      8           WHEN :mortage_balance <= 19999 THEN 'B'
      9           ELSE 'C'
     10      END")
     11  begindata
     12  1
     13  9999
     14  10000
     15  19999
     16* 20000
    
    SQL> host sqlldr scott/tiger c:\temp\bla.ctl
    
    SQL> select * from mortage;
    
    MORTAGE_BALANCE M
    --------------- -
                  1 A
               9999 A
              10000 B
              19999 B
              20000 C
    
    SQL>
    P.S. Bugger! While I was preparing my demonstration example, Gert (denevge) allready pointed in the right direction.

    "It just ain't fair", said he, crying his tears out....

    [Edited by jmodic on 10-03-2002 at 07:52 AM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Smile

    Bloody Hell lads, I'm on my way to test. If your advice doesn't crack it, then I don't know what will.

    Thanx...

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    I tried the trigger option on insert, but when I direct load into the table, it disables all triggers first !

    However, we've come up with a cunning plan on the SQLLDR control file ......

    MORTGAGE_BALANCE POSITION(409:417) INTEGER EXTERNAL ,
    MORTGAGE_CLASS "(FLOOR(:MORTGAGE_BALANCE/10000) + 1)"

    This provides classing values of 1, 2, 3, 4 etc., instead of A, B, C, D etc, but is just as appropriate.

    Didn't try the CASE statement, but that was probably the next step.....

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