Home » RDBMS Server » Server Utilities » load conditional column data into oracle table-column
load conditional column data into oracle table-column [message #313152] Thu, 10 April 2008 22:11 Go to next message
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 #313191 is a reply to message #313152] Fri, 11 April 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not using external table, it is far more easier to create a SQL query than a SQL*Loader control file.

Regards
Michel
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 messageGo to next message
Littlefoot
Messages: 20900
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 #313414 is a reply to message #313199] Fri, 11 April 2008 16:09 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you very much!
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 messageGo to next message
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 #315519 is a reply to message #313152] Mon, 21 April 2008 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>i have to load data only frist record instead of two or three reocord.
How do you know the 1st record contains the correct data?

>How can i take care this in sql*loader?
Don't use SQL*Loader to resolve this.

>If you have another suggestion, could you please give me an example?
Apply a UNIQUE index on the table & have duplicates rejected by the UNIQUE constraint.


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 messageGo to next message
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 #315575 is a reply to message #315566] Tue, 22 April 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, don't use SQL*Loader, use an external table.

And don't put your whole post between code tags, just code. Check with Preview button your lines don't exceed 80 characters.

Regards
Michel

[Updated on: Tue, 22 April 2008 10:31]

Report message to a moderator

Re: load conditional column data into oracle table-column [message #315719 is a reply to message #315575] Tue, 22 April 2008 09:37 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Micheal,
did you see my MERGE statements?
What i am doing wrong?

Thanks,
Re: load conditional column data into oracle table-column [message #315737 is a reply to message #315719] Tue, 22 April 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is wrong is
a/ you can't use a select column alias in where clause
b/ you can't use an analytic fucntion in where clause

Regards
Michel
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 messageGo to next message
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 #315758 is a reply to message #315745] Tue, 22 April 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't have your tables
I don't have your constraints
I don't have the current data
I don't have the data you want to load
I can't answer.

Regards
Michel
Re: load conditional column data into oracle table-column [message #315793 is a reply to message #315745] Tue, 22 April 2008 15:41 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
poratips
i am getting duplicate data in my incoming file and i have to load data only frist record

But, why would you want to worry about it? What's wrong with Anacedent's suggestion regarding UNIQUE index? Let Oracle handle duplicates!
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 messageGo to next message
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 #315838 is a reply to message #315831] Tue, 22 April 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So post a test case.

Regards
Michel
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
flyboy
Messages: 1832
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>
Previous Topic: Sql*loader - How can I skip filed from tab delimitted file?
Next Topic: create db
Goto Forum:
  


Current Time: Wed Dec 07 20:32:58 CST 2016

Total time taken to generate the page: 0.05678 seconds