If SALES =  01  then the DEPT_NAME = SAN
If SALES =  02  then the DEPT_NAME = GOOD
If SALES =  03  then the DEPT_NAME = GREAT
If SALES =  04  then the DEPT_NAME = CLEAN

DECODE(SALES,  '01','SAN',  '02', 'GOOD',  '03', 'GREAT',  '04', 'CLEAN')

If SALES =  01 and DEPT = 01 then the DEPT_NAME = SAN
If SALES =  02 and DEPT = 09 then the DEPT_NAME = GOOD
If SALES =  02 and DEPT = 15 then the DEPT_NAME = SOLD ==> this one added with different dept
If SALES =  03 and DEPT = 10 then the DEPT_NAME = GREAT
If SALES =  04 and DEPT = 11 then the DEPT_NAME = CLEAN

truncate table TARGET_table
/
Insert into TARGET_TABLE (
 ID,
 DESC   ,
 DEPT         ,
 SALES        ,
 DEPT_NAME
)
select
ID,
DESC   ,
 DEPT         ,
 SALES        ,
 DECODE(SALES,  '01',  'SAN',  '02',  'GOOD',  '03',  'GREAT',  '04', 'CLEAN')
from  (select ID,
 DESC   ,
 SALES             ,
 DECODE(SALES,  '01',  'SAN',  '02',  'GOOD',  '03',  'GREAT',  '04', 'CLEAN') ,
row_number() over (partition by  id, sales, DEPT order by id, sales, DEPT desc) rnum
from SOURCE
)
  Where rnum = 1 and sales IN ('01', '02', '03', '04')
and DEPT is not null
/
commit;

SELECT SALES, 
CASE SALES when '01' and dept 01 then 'SAN'
               when '02' and DEPT 09 then 'SOLD'
               when '02' and DEPT 15 then 'GOOD'
               ELSE 'NO DEPT' END
FROM TARGET WHERE sales IN ('01', '02','03','04')

IND> select sal,deptno,case when deptno =30 and sal between 800 and 1300 then 'sriram'
  2  else 'DAWN' end
  3  from emp;
       800         20 DAWN
      1600         30 DAWN
      1250         30 sriram
      2975         20 DAWN
      1250         30 sriram
      2850         30 DAWN
      2450         10 DAWN
      3000         20 DAWN
      5000         10 DAWN
      1500         30 DAWN
      1100         20 DAWN
       950         30 sriram
      3000         20 DAWN
      1300         10 DAWN
      7000            DAWN

15 rows selected.

IND> ed
Wrote file afiedt.buf

  1  select sal,deptno,case deptno when 30 and sal between 800 and 1300 then 'sriram'
  2  else 'DAWN' end
  3  from emp
  4* where hiredate in('17-DEC-80','12-JAN-83')
IND> /
select sal,deptno,case deptno when 30 and sal between 800 and 1300 then 'sriram'
                                      *
ERROR at line 1:
ORA-00905: missing keyword

load data
infile *
into table target_table
fields terminated by ','
trailing nullcols
(sales,
 dept,
 dept_name "case when :sales='01' and :dept='01' then 'SAN'
                 when :sales='02' and :dept='09' then 'GOOD'
                 when :sales='02' and :dept='15' then 'SOLD'
                 when :sales='03' and :dept='10' then 'GREAT'
                 when :sales='04' and :dept='11' then 'CLEAN'
            end")
begindata:
01,01,
02,09,
02,15,
03,10,
04,11,

SCOTT@orcl_11g> create table target_table
  2    (sales	   varchar2 ( 4),
  3  	dept	   varchar2 ( 4),
  4  	dept_name  varchar2 (15))
  5  /

Table created.

SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11g> select * from target_table
  2  /

SALE DEPT DEPT_NAME
---- ---- ---------------
01   01   SAN
02   09   GOOD
02   15   SOLD
03   10   GREAT
04   11   CLEAN

SCOTT@orcl_11g>

load data
infile 'dept.txt'
into table target_table
fields terminated by ','
trailing nullcols
(sales,
 dept,
 dept_name "case when :sales='01' and :dept='01' then 'SAN'
                 when :sales='02' and :dept='09' then 'GOOD'
                 when :sales='02' and :dept='15' then 'SOLD'
                 when :sales='03' and :dept='10' then 'GREAT'
                 when :sales='04' and :dept='11' then 'CLEAN'
                 when :sales='05' and :dept='12' then 'dept5'
                 when :sales='06' and :dept='13' then 'dept6'
                 when :sales='07' and :dept='01' then 'dept7'
                 when :sales='08' and :dept='09' then 'dept8'
                 when :sales='09' and :dept='15' then 'dept9'
                 when :sales='10' and :dept='10' then 'dept10'
                 when :sales='11' and :dept='11' then 'dept11'
                 when :sales='12' and :dept='12' then 'dept12'
                 when :sales='13' and :dept='13' then 'dept13'
                 when :sales='14' and :dept='12' then 'dept14'
                 when :sales='15' and :dept='13' then 'dept15'
                 ELSE 'NO DEPT'
            end")

Error:
Token longer than max allowable length of 258 chars
      WHEN :sales= '01' AND :dept='01' then 'SAN'