Re: sqlloader

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/08/28
Message-ID: <5u35bk$l2r@bgtnsc02.worldnet.att.net>#1/1

If you treat the data in your input file as one field (num), try this:

....
DECODE (SUBSTR(num,4,1), '-', (TO_NUMBER(SUBSTR(num,1,3))*-1), TO_NUMBER(SUBSTR(num,1,3))) POSITION (1:4) CHAR

Mind you, this will work (if it works at all - I haven't tested it) only if your input data is always as shown in your example (i.e., fixed length and format, with three digits followed by the +/- character). Otherwise, you could try using the INSTR function to identify the position of the +/- character in the field.

Hope this helps.

Michael Serbanescu

---

james wrote:
>
> Does anyone know how to test a fiels in sqlload without it being a column
> in the database?
>
> ie:
>
> table is as follows.
>
> create table test (
> num number(7,0),
> sign char(1)
> );
>
> input file has 2 fields, a number and a sign.
>
> 100-
> 200+
>
> sqlload script is as follows:
>
> LOAD DATA
> INFILE '..\data\call_details.dat'
> REPLACE
> INTO TABLE call_details
> TRAILING NULLCOLS
> (
> sign POSITION(4:4) char,
> decode(sign, '-', (num * -1), num) POSITION(1:3) INTEGER EXTERNAL
> )
>
> If I remove the sign column I can no longer perform this test. I need to
> get rid of it somehow.
Received on Thu Aug 28 1997 - 00:00:00 CDT