load conditional column data into oracle table-column [message #313152] |
Thu, 10 April 2008 22:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
I have oracle 9i on Sun sloaris and i need to load data in one of oracle table using sql*loader with conditional column data.
Please let me know if you need table script.
My table is like:
Load_table
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
---
---
---
Now i have to load data like:
If col2 = US1 then col3 = 'AA'
If col2 = US2 then col3 = 'BB'
If col2 = US3 then col3 = 'CC'
How can i load this data in table using sql*loader?
Thanks,
Pora
[Updated on: Fri, 11 April 2008 00:43] by Moderator Report message to a moderator
|
|
|
|
Re: load conditional column data into oracle table-column [message #313199 is a reply to message #313152] |
Fri, 11 April 2008 01:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
DECODE might help; here's an example: this is how the control file looks like:load data
infile *
replace
into table test
fields terminated by ','
trailing nullcols
(col1,
col2,
col3 "decode(:col2, 'US1', 'AA', 'US2', 'BB', 'US3', 'CC')"
)
begindata
Little,US1,
fOOT,US1,blabla
Big,US2,
Mama,US3,howyesno
Loading session and checking what we've done (snippet):C:\Temp>sqlldr scott/tiger@ora10 control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pet Tra 11 08:13:59 2008
Commit point reached - logical record count 3
Commit point reached - logical record count 4
C:\Temp>sqlplus scott/tiger@ora10
SQL*Plus: Release 10.2.0.1.0 - Production on Pet Tra 11 08:14:04 2008
SQL> select * From test;
COL1 COL2 COL3
---------- ---------- ----------
Little US1 AA
fOOT US1 AA
Big US2 BB
Mama US3 CC
SQL>
|
|
|
|
Re: load conditional column data into oracle table-column [message #315516 is a reply to message #313414] |
Mon, 21 April 2008 16:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
Thanks, DEcode works fine.
I have another question as i am getting duplicate data in my incoming file and i have to load data only frist record
instead of two or three reocord.
How can i take care this in sql*loader?
If you have another suggestion, could you please give me an example?
My data is like:
A1 B1 us01 pl1
A1 B1 us01 pl2
A1 B1 us01 pl3
I want to load only 1st record as records are almost same except pl...
Thanks,
[Updated on: Tue, 22 April 2008 00:36] by Moderator Report message to a moderator
|
|
|
|
Re: load conditional column data into oracle table-column [message #315566 is a reply to message #315519] |
Mon, 21 April 2008 22:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Thanks for your response.
I confirmed with user and it's just differ by plant as everything will remain same so i can load first/one record out of two or three.
I have created another LOAD table for incoming file in my DB and trying to insert/update using oracle MERGE in our existing tables but somehow it throws an error when i use parttion features to select 1st record based on row_num.
MERGE INTO target T
USING (Select u.*, row_number() over (partition by id ORDER BY id, org, D_name) rn
from LOAD_table u Where s.rn = 1) S
ON (T.ID = S.ID)
WHEN MATCHED THEN
...
...
but it throws an error for Select u.*, row_number() over (partition by id ORDER BY id, org, D_name
[Updated on: Tue, 22 April 2008 00:36] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: load conditional column data into oracle table-column [message #315745 is a reply to message #315737] |
Tue, 22 April 2008 10:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Thanks Michel,
I tried but still getting Unique constraint error while using MERGE but if i run individualy that select it runs fine, no duplictes.
MERGE INTO target T
USING (Select * from (select row_number() over (partition by id ORDER BY id desc) rn, T.*
from LOAD_table Where rn = 1) S
ON (T.ID = S.ID)
WHEN MATCHED THEN
...
If i run select, it's fine without duplicates.
Select * from (select row_number() over (partition by id ORDER BY id desc) rn, T.*
from LOAD_table Where rn = 1
|
|
|
|
|
Re: load conditional column data into oracle table-column [message #315831 is a reply to message #315793] |
Tue, 22 April 2008 22:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Thanks, finally i created UK/PK after keep trying to handle in MERGE but it was keep throwing error even i have checked askTom site and tried to get example with analytical function in MERGE but it was throwing Unique constraint error and i couldn't understand why.
Appreciated your help.
Regards,
poratips
|
|
|
|
Re: load conditional column data into oracle table-column [message #315849 is a reply to message #313152] |
Wed, 23 April 2008 00:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> but it was throwing Unique constraint error and i couldn't understand why.
As stated in the MERGE Statement description: Quote: | MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.
|
Most probably, you have "duplicates" in the source data. If you do not mind, which of them updates the destination table, use aggregation: SELECT <the columns you join, here only ID>,
<aggregates on the other columns, eg. MIN(D_NAME)>
FROM <source table>
GROUP BY <the columns you join, here only ID>
|
|
|