First position of a delimited file is skipped when I read it using SQL LOADER control file

Question

I have a delimited file that I am reading using a Cntrl file. The first position value from all content lines is being skipped.

Data file:

ROWID_OBJECT||LAST_UPDATE_DATE||EPR_ROWID||ORGANISATION_TYP||LEGL_CAPCTY||CNTRY_OF_INCORP||ESTABLISHMENT_DATE||MAIN_BRNCH_IND||MAIN_COC||BRNCH_COC||DUNS_NMBR||BVD_NMBR||CLOSURE_DATE||PUBLIC_ENT_TYPE||BY_LAWS_DATE
379           ||06/12/2019 18:33:39||34745         ||027||||NL||01/01/1995 00:00:00||1||33665455||000017469892||418469706||||||||06/21/1995 00:00:00

Control file:

load data
  infile "abcd.DAT"
  into table TAB_RUNTAB
  append
  WHEN (1:13) = 'SNAPSHOTDATE:'
  --TRAILING NULLCOLS
 (
    FILENAME CONSTANT 'FILE.DAT',
    TYPE CONSTANT 'FILE' , 
    LOADED "SYSDATE" 
 )
  into table TAB_ORGANISATION
  append
  WHEN (1:4) <> 'SNAP' AND (1:3) <> 'ROW'
  fields  terminated by "||" 
  (ROWID_OBJECT        ,
  LAST_UPDATE_DATE     ,
  EPR_ROWID            ,
  ORGANISATION_TYP     ,
  LEGL_CAPCTY          ,
  CNTRY_OF_INCORP      ,
  ESTABLISHMENT_DATE   ,
  MAIN_BRNCH_IND       ,
  MAIN_COC             ,
  BRNCH_COC            ,
  DUNS_NMBR            ,
  BVD_NMBR             ,
  CLOSURE_DATE         ,
  PUBLIC_ENT_TYPE      ,
  BY_LAWS_DATE        DATE "mm/dd/yyyy hh24:mi:ss"
)

Here ROWID_OBJECT column only gets value as 79 instead of 379. Any suggestion is appreciated.

Answer 1

I believe you need to use the position(1) clause to tell sqlloader to go back to the beginning of the line after querying the positions 1:4 and 1:3. Replace this line and I bet you will be back in business:

(ROWID_OBJECT  POSITION(1)      ,