| how to selectively load records using SQL LOADER [message #158772] |
Tue, 14 February 2006 05:46  |
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
|
Junior Member |
|
|
hi there,
I have a SQL LOADER script like the one below
LOAD DATA
INFILE billhistory.txt
replace
INTO TABLE spain.spainmigbillhistory
fields terminated by ';' optionally enclosed by "'"
TRAILING NULLCOLS
( TPH_BILL_NUM ,
TPH_BILL_ACCOUNT_NUM,
TPH_ACCOUNT_NUM,
DUMMY_FIELD2 FILLER,
NOMINAL_BILL_DATE "TO_DATE(:NOMINAL_BILL_DATE,'dd/mm/yyyy')",
ACTUAL_BILL_DATE "TO_DATE(:ACTUAL_BILL_DATE,'dd/mm/yyyy')",
DUMMY_DATE FILLER,
DUMMY_DATE2 FILLER,
INVOICE_NET,
VAT_RATE,
VAT_AMOUNT,
PREVIOUS_BALANCE,
RESULT,
INVOICE_GROSS,
BALANCE_OUTSTANDING,
TOTAL_PAYMENTS,
LAST_INVOICE_BALANCE_FWD,
GENEVA_BILL_SEQ)
How do I get it to not load up records where the tph_bill_num does not begin with a C.
Any ideas ????
|
|
|
|
| Re: how to selectively load records using SQL LOADER [message #158778 is a reply to message #158772] |
Tue, 14 February 2006 06:16  |
vban2000
Messages: 207
Registered: March 2005
|
Senior Member |
|
|
One example here..
| Quote: |
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
|
regards
AnDy
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
