Using negative number in WHEN clause using SQL*Loader...?

[slicendice]

Hi
Does anyone know if it's possible to check for a negative number using the WHEN clause in SQL*Loader? The column being checked is defined in the database table as NUMBER(8).

I can use the following OK:

WHEN (col1 <> '1')

The above works fine, but if I change it to:

WHEN (col1 <> '-1')

...it just seems to ignore it and load the data anyway, even if col1 is -1.

I've also tried using the Hex option with:

WHEN (col1 <> X'-1')

...but this just causes the error:

SQL*Loader-350: Syntax error at line 5.
Non-hex character encountered where hex char expected
WHEN (col1 <> X'-1')

If anyone has any ideas, I'd be most grateful

Thanks

 

[Andrzejek]

If your field is defined as NUMBER(8), why are you using a String expression:
[tt]WHEN (col1 <> [red]'-1'[/red])[/tt] ?

Wouldn't that be:
[tt]WHEN (col1 <> -1)[/tt] ?


Have fun.

---- Andy

 

[slicendice]

As far as I'm aware the WHEN clause only deals with strings...though I stand to be corrected on that one.

I tried it anyway, just in case, but it simply caused another error:

SQL*Loader-350: Syntax error at line 5.
Expecting quoted string or hex identifier, found "-".
WHEN (ptau_per_id <> -1)
                     ^

 

[LKBrwnDBA]

Forget SQL*Loader and create external table.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[slicendice]

I have been looking at external tables but I use an Oracle function on one of the data items as it is imported, i.e. in SQL*Loader control file:

LOAD DATA
INFILE 'udf_data2.txt'
BADFILE 'udf_data2.bad'
APPEND INTO TABLE udf_test
FIELDS TERMINATED by ','
(ptau_id       "seq_udf_test.nextval",
 ptau_per_id   "fnc_get_sys_id_from_leg_id(:ptau_per_id)",
 ptau_a_sens2  char,
 ptau_a_type   char)

So the value put into field UDF_TEST.PTAU_PER_ID is actually a derived field using the tau_per_id as the source.

Is it possible to do this using external tables?

 

[LKBrwnDBA]

What is the actual format of the source file?
Your control file does not seem right unless there is an empty first field in the file.

When you define external table, the data appears as a db table with the values from the file "as is". All you do then is use normal sql statements to query the table:

INSERT INTO udf_test
  SELECT seq_udf_test.NEXTVAL ptau_id
       , fnc_get_sys_id_from_leg_id( ptau_per_id )
       , ptau_a_sens2
       , ptau_a_type
    FROM udf_data2_xt;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

slice,

using external tables puts the full power of SQL at your disposal. You can perform significant transformation on external table data during loading.

lkbrwn is right, dump loader and go for external tables.

Regards

T