Decoding in SQL Loader 1

[Finubar]

Hi from the Emerald Isle

I am trying to load data from a flat file into 3 seperate tables. The tables contain numerous different columns and for the purpose of this job most can be filled with constants.

my problem lies in the that the flat file contains a county name description but 2 of the tables i am trying to load into the corresponding columns only take a numeric value. we have an upload program that decodes these descriptions into the numeric values during a batch run(nightly) but for my current job i cannot use the batch job.

when i try to hard code in the descriptions and their corresponding values(33 entries exceeding 256 characters) in to the decode statement in the SQL Loader file i get told that this exceeds the max allowed.

is there anyway i can get around this ?

or

can i reference the static table from the .ctr file ?

 

[spenglerr]

sorry finubar, I do not understand your question completely. So I will answer two meanings.

If you intend to use decoding for assigning numbers, then you can use it in a control file in double quotes.

If you have problems with the string length exceeding 256 characters ín free format, you will have to specify the char().

Example:

LOAD DATA
INFILE evt_imp.csv
REPLACE
INTO TABLE event_importer
FIELDS TERMINATED BY ';' optionally enclosed by '"'
TRAILING NULLCOLS
(
event_id
,name
,strasse
,plz
,ort
,ort_typ
,vorwahl_telefon
,vorwahl_fax
,email
,ueberschrift char(1000) terminated by ';'
,thema
,code "decode(code,'XXL',1,'XL',2,'L',3,'M',4,5)"
,kurztext char(2000) terminated by ';'
,langtext char(4000) terminated by ';'
,startdatum date(10) "DD.MM.YYYY"
,enddatum date(10) "DD.MM.YYYY"
,beginn
,link
,veranstalter char(2048) terminated by ';'
,veranstalter_url
,veranstalter_email
,mand_id
,event_provider_name CONSTANT EXCEL
)