SQL LOADER delimited.word

[Guybrush]

Hi!

I have a problem loading a text file with sql loader.
my control file is:

load data
insert into table ACTIVOS_TEMP2
WHEN (C_CLASE_ID='Z02212')
fields terminated by X'09' TRAILING NULLCOLS
( C_ACTIVO_ID position(1:4096) "delimited.word(:C_ACTIVO_ID,1,chr(34),chr(09))",
C_SUBNUMERO_ID position(1:1) "delimited.word(:C_ACTIVO_ID,2,chr(34),chr(09))",
C_SUPRANUMERO position(1:1) "delimited.word(:C_ACTIVO_ID,3,chr(34),chr(09))",
D_ACTIVO1 position(1:1) "delimited.word(:C_ACTIVO_ID,4,chr(34),chr(09))",
D_ACTIVO2 position(1:1) "delimited.word(:C_ACTIVO_ID,5,chr(34),chr(09))",
C_CLASE_ID position(1:1) "delimited.word(:C_ACTIVO_ID,6,chr(34),chr(09))",
C_TIPO_ID position(1:1) "delimited.word(:C_ACTIVO_ID,7,chr(34),chr(09))",
C_SUBTIPO_ID position(1:1) "delimited.word(:C_ACTIVO_ID,8,chr(34),chr(09))",
F_CAPITALIZACION position(1:1) "delimited.word(:C_ACTIVO_ID,9,chr(34),chr(09))",
D_ANOS_VIDA position(1:1) "delimited.word(:C_ACTIVO_ID,10,chr(34),chr(09))",
D_MES_VIDA position(1:1) "delimited.word(:C_ACTIVO_ID,11,chr(34),chr(09))",
C_ORDEN position(1:1) "delimited.word(:C_ACTIVO_ID,12,chr(34),chr(09))",
C_MODO_ADQUISICION position(1:1) "delimited.word(:C_ACTIVO_ID,15,chr(34),chr(09))",
C_ANOS_VIDA_REAL position(1:1) "delimited.word(:C_ACTIVO_ID,10,chr(34),chr(09))",
C_ESTADO_BIEN position(1:1) "delimited.word(:C_ACTIVO_ID,16,chr(34),chr(09))",
D_ELEMENTO_PEP position(1:1) "delimited.word(:C_ACTIVO_ID,17,chr(34),chr(09))",
D_OBSERVACIONES position(1:1) "delimited.word(:C_ACTIVO_ID,18,chr(34),chr(09))",
F_BAJA position(1:1) "delimited.word(:C_ACTIVO_ID,42,chr(34),chr(09))"
)
into table ITA_Z022_TEMP
WHEN C_CLASE_ID='Z02212'
fields terminated by X'09' TRAILING NULLCOLS
(
C_ACTIVO_ID position(1:4096) "delimited.word(:C_ACTIVO_ID,1,chr(34),chr(09))",
C_SUBNUMERO_ID position(1:1) "delimited.word(:C_ACTIVO_ID,2,chr(34),chr(09))",
D_NUM_PATENTE position(1:1) "delimited.word(:C_ACTIVO_ID,45,chr(34),chr(09))",
F_CONCESION position(1:1) "delimited.word(:C_ACTIVO_ID,46,chr(34),chr(09))",
F_CADUCIDAD position(1:1) "delimited.word(:C_ACTIVO_ID,47,chr(34),chr(09))",
D_NUM_BOPI position(1:1) "delimited.word(:C_ACTIVO_ID,48,chr(34),chr(09))",
D_LOGOTIPO position(1:1) "delimited.word(:C_ACTIVO_ID,49,chr(34),chr(09))",
C_CLASE_ID position(1:1) "delimited.word(:C_ACTIVO_ID,6,chr(34),chr(09))"
)

But it says me that the clause WHEN failed.

thank you.

[tamilselvan]

Did you install "delimited" pkg?

Tamil

[Guybrush]

Yes, I have installed this package. If the control file doesn't have a WHEN clause it runs ok, but I want to depends on what is the value of the field C_CLASE_ID, the register load in a table or in another.

[WilliamR]

SQL*Loader's WHEN clauses are very basic. I haven't tried your example but I'm pretty sure it can't read a derived field value via an SQL function. It can only read literal values from the file.

Does DELIMITED.WORD just split a string into tokens? If so, why can't you just define the field separators using SQL*Loader itself?

[Guybrush]

ok. thank you!
at last I have load the data in one table, but I tried to load in multiple tables using the delimited.word function from the field which was the when clause and it worked.

load data
append into table ITA_ACTIVOS_TEMP2
fields terminated by X'09' TRAILING NULLCOLS
( C_ACTIVO_ID position(1:4096) "delimited.word(:C_ACTIVO_ID,1,chr(34),chr(09))",
C_SUBNUMERO_ID position(1:1) "delimited.word(:C_ACTIVO_ID,2,chr(34),chr(09))",
C_SUPRANUMERO position(1:1) "delimited.word(:C_ACTIVO_ID,3,chr(34),chr(09))",
...

)

into table ITA_Z022_TEMP
WHEN C_CLASE_ID='Z02212'
fields terminated by X'09' TRAILING NULLCOLS
( C_ACTIVO_ID,C_SUBNUMERO_ID,C_SUPRANUMERO,D_ACTIVO1,D_ACTIVO2,C_CLASE_ID,
C_ACTIVO_ID2 position(1:4096) "delimited.word(:C_ACTIVO_ID2,1,chr(34),chr(09))",
D_NUM_PATENTE position(1:1) "delimited.word(:C_ACTIVO_ID2,45,chr(34),chr(09))",
F_CONCESION position(1:1) "delimited.word(:C_ACTIVO_ID2,46,chr(34),chr(09))",
F_CADUCIDAD position(1:1) "delimited.word(:C_ACTIVO_ID2,47,chr(34),chr(09))",
D_NUM_BOPI position(1:1) "delimited.word(:C_ACTIVO_ID2,48,chr(34),chr(09))",
D_LOGOTIPO position(1:1) "delimited.word(:C_ACTIVO_ID2,49,chr(34),chr(09))"
)