OraFAQ Forum: Server Utilities » SQL Loader: Filtering data

Home » RDBMS Server » Server Utilities » SQL Loader: Filtering data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader: Filtering data [message #269214]

Fri, 21 September 2007 03:26

tessm
Messages: 5
Registered: September 2007
Location: Philippines

Junior Member

Hi guys, i need help pls.

currently im doing sqlloader script (UNIX flat form). But records with negative value (less than 0) of TRANS_AMT will be discarded.

i dont know how to incorporate the said requirement to my script. pls, see attached file.

thanks

Attachment: TELESULIT.sh
(Size: 1.64KB, Downloaded 1439 times)

[Updated on: Fri, 21 September 2007 03:28]

Report message to a moderator

Re: SQL Loader: Filtering data [message #269270 is a reply to message #269214]

Fri, 21 September 2007 07:36

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I didn't get your question.

Quote:

But records with negative value (less than 0) of TRANS_AMT will be discarded.

Quote:

how to incorporate the said requirement

Using your script, do you want to filter those rows?

By
Vamsi

Report message to a moderator

Re: SQL Loader: Filtering data [message #269295 is a reply to message #269214]

Fri, 21 September 2007 09:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You could place a check constraint on TRANS_AMT such that it must be >= 0

Report message to a moderator

Re: SQL Loader: Filtering data [message #269332 is a reply to message #269295]

Fri, 21 September 2007 13:33

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I can't read this .sh file (it isn't a pure TXT file, right?), but - couldn't you just include a WHEN clause into the control file? As I've understood your initial message, you want to discard all records where 'trans_amt < 0'; if so, you could try with something like this:

LOAD DATA
...
INTO TABLE your_table
WHEN trans_amt >= 0
...

See the "Loading Records Based on a Condition" section of the SQL*Loader Control File Reference document.

Report message to a moderator

Re: SQL Loader: Filtering data [message #269864 is a reply to message #269332]

Mon, 24 September 2007 22:29

tessm
Messages: 5
Registered: September 2007
Location: Philippines

Junior Member

hi Vamsi,

yes, i need to filter those rows.

Report message to a moderator

Re: SQL Loader: Filtering data [message #269865 is a reply to message #269332]

Mon, 24 September 2007 22:32

tessm
Messages: 5
Registered: September 2007
Location: Philippines

Junior Member

Hi Littlefoot,

Actually, i tried that already. Unfortunately, the WHEN clause does not support all the SQL operators, just '='.

Report message to a moderator

Re: SQL Loader: Filtering data [message #269866 is a reply to message #269295]

Mon, 24 September 2007 22:34

tessm
Messages: 5
Registered: September 2007
Location: Philippines

Junior Member

anacedent wrote on Fri, 21 September 2007 09:26

You could place a check constraint on TRANS_AMT such that it must be >= 0

Thanks for this. Since WHEN clause does not support all the SQL operators (except '='), i think this is the best thing that i can do. check constraints...

Report message to a moderator

Re: SQL Loader: Filtering data [message #269868 is a reply to message #269214]

Mon, 24 September 2007 22:35

tessm
Messages: 5
Registered: September 2007
Location: Philippines

Junior Member

Guys, thank you very much for helping me to solve this.

Report message to a moderator

Previous Topic:	sqlloader error
Next Topic:	Problem with data pump

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 09:52:50 CST 2024