-
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..........
-
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.
-
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
-
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
-
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
-
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 ?
-
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
-
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?
-
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...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|