INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE

WHEN (col1 <> '1')

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

CODE

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:

CODE

WHEN (col1 <> X'-1')
...but this just causes the error:

CODE

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

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

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

Wouldn't that be:
WHEN (col1 <> -1)  ?
 

Have fun.

---- Andy

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

(OP)
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:

CODE

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

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



Forget SQL*Loader and create external table.

3eyes
 

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

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

(OP)
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:

CODE

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 :ptau_per_id as the source.

Is it possible to do this using external tables?  

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



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:

CODE

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;
noevil
 

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close