Hello,

we have a requirement to load multiple data based on different conditions. The details are as below,

Create:

create table table_a (empno number, empname varchar2(50),salary number, status varchar2(30))
/

Control file:
options  ( skip=1 )
LOAD DATA
INFILE "C:\Users\data.txt"
APPEND 
INTO TABLE table_a WHEN Grade = 'Clerk' 
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(empno,
empname,
salary,
grade
)
INTO TABLE table_a WHEN Grade = 'Peon' 
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(empno,
empname,
salary,
grade
)
INTO TABLE table_a WHEN Grade = 'Officer' 
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(empno,
empname,
salary,
grade
)
INTO TABLE table_a WHEN Grade = 'Manager' 
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(empno,
empname,
salary,
grade
)

Data file:
"empno"|"empname"|"salary"|"grade"
"1"|"ABC1"|1000|"Clerk"
"2"|"ABC2"|1000|"Clerk"
"3"|"ABC3"|500|"Peon"
"4"|"ABC4"|500|"Peon"
"5"|"ABC5"|2000|"Officer"
"6"|"ABC6"|2000|"Officer"
"7"|"ABC7"|2000|"Officer"
"8"|"ABC8"|2000|"Officer"
"9"|"ABC9"|2000|"Officer"
"10"|"ABC10"|5000|"Manager"

While running sqlldr command, getting below error,

Record 10: Discarded - failed all WHEN clauses.

please suggest how to implement multiple when conditions in 1 control file.

Thanks

SQL*Loader: Release 12.2.0.1.0 - Production on Tue Sep 4 14:22:19 2018

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 9
Commit point reached - logical record count 10

Table TABLE_A:
  2 Rows successfully loaded.

Table TABLE_A:
  2 Rows successfully loaded.

Table TABLE_A:
  5 Rows successfully loaded.

Table TABLE_A:
  1 Row successfully loaded.