| sqlloader multiple into table clause [message #340137] |
Mon, 11 August 2008 11:08  |
brunins
Messages: 3
Registered: August 2008
|
Junior Member |
|
|
Hi everybody,
I have a problem loading a Jboss properties file into an oracle table using sqlloader with multiple into table clauses.
Here's a sample of the source data:
prestation.activitycode.BOX_OFFICE_INIT=Opleiding Loket
prestation.activitycode.BOX_OFFICE_RFT=Versterking Loket
prestation.activitycode.CAR_MAINTENANCE=Onderhoud Wagenpark
premium.grouping.CASH=Kasvergoeding
premium.grouping.COMMERCIAL=Commercieel Attach\u00e9
premium.grouping.MEAL_VOUCHER=Maaltijdcheque
prestation.filter.filterResources=Filter lijst met medewerkers
prestation.filter.label.assigned=Toegewezen
prestation.filter.label.contractual=Contractueel baremiek
...
As you can see the input file is organized as key-value pairs separated by '='. Now I want to apply a filter while loading to exclude unwanted records, only the records beginning 'prestation.activitycode' and 'premium.grouping'.
I've created following control file:
OPTIONS (SKIP=3)
LOAD DATA
TRUNCATE
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:16) = 'premium.grouping'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:23) = 'prestation.activitycode'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
I skip the first 3 records( -> header records). If value of first 16 charchters is equal to 'premium.grouping' I do a substring to extract only the part after the last dot.
I take the value as is for the second column (-> description) and for the third column (language) I use the constant 'NL'.
Second into table clause is practically the same except that the condition in the WHEN clause is different.
As a result only the 'premium.grouping' are inserted. Nothing is inserted for 'prestation.activitycode' records. Sqlloader generates following data errors: Rejected - Error on table MISSING_DESCRIPTIONS, column CODE.
Column not found before end of logical record (use TRAILING NULLCOLS).
Adding trailing nullcols to the control file, inserts NULL records into my target table. 
Everyhting goes well when i process the into table clauses separatly.
What is wrong in my control file ?
sqlloader log in attachment.
Thanks for you precious help.
-
Attachment: log.log
(Size: 12.00KB, Downloaded 1218 times)
|
|
|
|
|
|
| Re: sqlloader multiple into table clause [message #340287 is a reply to message #340144] |
Tue, 12 August 2008 03:41  |
brunins
Messages: 3
Registered: August 2008
|
Junior Member |
|
|
Thanks Michel for your quick reply.
You suggest me using the position parameter in my second into table clause. But how is this possible with variable length values. The position expects begin-end parameters such as POSITION(25:39).
For eg. How do I substract the CAR_MAINTENANCE part in value 'prestation.activitycode.CAR_MAINTENANCE' ? I do know where it begins (after 'prestation.activitycode.') but not where it ends !
Thx in advance,
Cheers
|
|
|
|
| Re: sqlloader multiple into table clause [message #340301 is a reply to message #340287] |
Tue, 12 August 2008 04:41 |
brunins
Messages: 3
Registered: August 2008
|
Junior Member |
|
|
Ok I found it ! 
I my second WHEN clause I 've added POSITION(1).
My correct control looks like:
OPTIONS (SKIP=3)
LOAD DATA
TRUNCATE
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:16) = 'premium.grouping'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:23) = 'prestation.activitycode'
FIELDS TERMINATED BY "="
( code POSITION(1) "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
thx Michel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
