Re: SQL Loader Decimal Fields Trailing Signs

Well, I figured it out. My code below was slightly off with regards to the starting position of FIELD4.

In Oracle, field4 is defined as NUMBER (8,4).

The value I am trying to load is 1234.5678- starting in position 42 for a length of 10.

In the control file I defined my field as follows:

FIELD4 POSITION(42:51) DECIMAL EXTERNAL "SUBSTR(:FIELD4,10,1) || SUBSTR(:FIELD4,1,9)" Pull the sign off the back and stick it on the front and the negative values load perfectly.

Lori

In article <8sq3u6$jhi$1_at_nnrp1.deja.com>,   Lori <bayerwh_at_my-deja.com> wrote:
>
>
> I have hunted everywhere and have yet to find the answer. Can SQL
> Loader handle numeric fields with fixed decimal points and trailing
> signs? When the data is loaded using the control file below, all
> values are loaded as positive values.
>
> Can I concatenate two fields in the control file and load them into
 one
> field?
>
> FIELD4 POSITION(42:51) DECIMAL EXTERNAL "SUBSTR(:FIELD4, 51, 1) ||
> SUBSTR(:FIELD4, 49, 9)"
>
> I haven't seen an example of this I just took a shot. I didn't get an
> error but I didn't get any data in the column either.
>
> I really want to avoid loading the sign into a separate column on a
> temporary table and then writing a stored procedure to apply the sign
> when the data is moved to the final table.
>
> My data file looks like this:
>
> T0144444TEST PARENT
> T024444411111TEST CHILD ONE88809/28/20000023.4567-
> T024444422222TEST CHILD TWO88809/28/20000023.4567-
> T024444433333TEST CHILD THR88809/28/20000023.4567-
>
> My control file looks like this:
>
> LOAD DATA
> REPLACE
> INTO TABLE DIS_TEST_1
> WHEN (1:3)= 'T01'
> (PARENT_KEY POSITION(4:8) CHAR,
> FIELD1 POSITION(9:23) CHAR)
> INTO TABLE DIS_TEST_2
> WHEN (1:3) = 'T02'
> (PARENT_KEY POSITION(4:8) CHAR,
> CHILD_KEY POSITION(9:13) CHAR,
> FIELD1 POSITION(14:28) CHAR,
> FIELD2 POSITION(29:31) INTEGER EXTERNAL,
> FIELD3 POSITION(32:41) DATE 'MM/DD/YYYY',
> FIELD4 POSITION(42:51) DECIMAL EXTERNAL)
>
> Thanks in advance
>
> Lori
> --
> Bayer Corp
> West Haven, CT
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Bayer Corp
West Haven, CT


Sent via Deja.com http://www.deja.com/
Before you buy.