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?

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

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>

A1 B1 us01 pl1 
A1 B1 us01 pl2 
A1 B1 us01 pl3

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
...
...

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

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>