Home » RDBMS Server » Server Utilities » SQL Loader: Filtering data
SQL Loader: Filtering data [message #269214] Fri, 21 September 2007 03:26 Go to next message
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 297 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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: SQL Loader: Filtering data [message #269295 is a reply to message #269214] Fri, 21 September 2007 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You could place a check constraint on TRANS_AMT such that it must be >= 0
Re: SQL Loader: Filtering data [message #269332 is a reply to message #269295] Fri, 21 September 2007 13:33 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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.
Re: SQL Loader: Filtering data [message #269864 is a reply to message #269332] Mon, 24 September 2007 22:29 Go to previous messageGo to next message
tessm
Messages: 5
Registered: September 2007
Location: Philippines
Junior Member

hi Vamsi,

yes, i need to filter those rows.
Re: SQL Loader: Filtering data [message #269865 is a reply to message #269332] Mon, 24 September 2007 22:32 Go to previous messageGo to next message
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 '='.

Re: SQL Loader: Filtering data [message #269866 is a reply to message #269295] Mon, 24 September 2007 22:34 Go to previous messageGo to next message
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...

Re: SQL Loader: Filtering data [message #269868 is a reply to message #269214] Mon, 24 September 2007 22:35 Go to previous message
tessm
Messages: 5
Registered: September 2007
Location: Philippines
Junior Member


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

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


Current Time: Wed Dec 07 04:52:04 CST 2016

Total time taken to generate the page: 0.21958 seconds