Re: SQL*Loader

"Mark Webb" <mwebb_at_cix.compulink.co.uk> wrote in article <E96xwu.EpM_at_cix.compulink.co.uk>...
> Hi everyone,
>
> I'm new to SQL*Loader. I've been presented with a
> data file to load and I'm having problems with a date
> field. For a date of birth sometimes the year is known
> but the month and day may not be. This has been
> represented in the source file as (for example) 19630000.
> The file has fields delimited by a plus (+) sign. The
> decision has been taken to split the date into year,month
> and day integers each of which can be null.
>
> My quest is, what do I need to put in my SQL*Loader
> control file to persuade it to read a single delimited
> field, split it into 3 actual columns and substitute NULLs
> in the even that any of the 3 columns contain all zeros?
> Or is this too much to ask? Any help *gratefully*
> received. Thanks.
>
> Mark W.

assuming 19630000 starts at position, eg. 33. then your control file might look like

YEAR		POSITION(33,36)	INTEGER EXTERNAL 	NULLIF  YEAR='0000',
MONTH		POSITION(37,38) 	INTEGER EXTERNAL 	NULLIF  MONTH='00',
DAY		POSITION(39,40)	INTEGER EXTERNAL	NULLIF  DAT='00',

etc

Cordially,
Serge

Send your humble donations to starving DBAs of Siberia. $120,000/year minimum contribution   Received on Fri Apr 25 1997 - 00:00:00 CDT