| SQL*Loader, Referencing fillier columns [message #263548] |
Thu, 30 August 2007 05:10  |
Barry Brierley
Messages: 10
Registered: November 2004
|
Junior Member |
|
|
Hi,
Apparantly you can't reference filler columns. Is there any other way I can take two physical columns from a file and use them to decide how to populate one column on the database?
To explain: Table XXSYK_DC_OHQ_STAGING is used by a number of other processes and has a field called LOT_SERIAL. However in the file I get either a lot number or a serial number. I was going to set the lot number and serial numbers as filler fields then use a decode to decide which one to use. changing the table is not an option.
If I can't get the right I have to leave it in SmartDB (yuck), create yet another staging table, or create a trigger on the table itself. My (incorrect) control file is below.
APPEND
INTO TABLE "XXSYK_DC_OHQ_STAGING"
APPEND
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(RECORD_NR "xxsyk_dc_ohq_staging_seq.nextval",
SOURCE constant "CONS",
SITE,
CUSTOMER_NR,
ORDER_NR,
ORDER_TYPE,
PART,
LOT_NUMBER filler,
EXPIRY_DATE,
SERIAL_NUMBER filler,
QUANTITY,
QUANTITY_SUM,
STATUS,
UNIT_MEASURE,
LD_REF filler,
SERIAL_OR_LOT "decode(:LOT_NUMBER, null, decode(:SERIAL_NUMBER,null,'None','Serial'), 'Lot')",
LOT_SERIAL "decode(:LOT_NUMBER, null, :SERIAL_NUMBER, :LOT_NUMBER)"
)
Thanks,
Barry
|
|
|
|
| Re: SQL*Loader, Referencing fillier columns [message #263585 is a reply to message #263548] |
Thu, 30 August 2007 08:03  |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
| Quote: | Apparantly you can't reference filler columns
|
What was the problem? Was there any error message?...
Regards
Michel
[Updated on: Thu, 30 August 2007 08:06] Report message to a moderator |
|
|
|
| Re: SQL*Loader, Referencing fillier columns [message #263600 is a reply to message #263585] |
Thu, 30 August 2007 08:37 |
Barry Brierley
Messages: 10
Registered: November 2004
|
Junior Member |
|
|
The error message I get is
SQL*Loader-291: Invalid bind variable LOT_NUMBER in SQL string for column SERIAL_OR_LOT.
Reading through the Oracle utilities manual this is because of the following:
"Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings. "
|
|
|
|
|
|
|
|
|
|
| Re: SQL*Loader, Referencing fillier columns [message #263643 is a reply to message #263619] |
Thu, 30 August 2007 10:28 |
Barry Brierley
Messages: 10
Registered: November 2004
|
Junior Member |
|
|
That doesn't help me then since I won't have server access.
It seems I can't use SQL*Loader to do any "complex" logic. I'll use SQL*Loader to load into a spare column then use a trigger to sort out the logic.
Not ideal but anything is better than SmartDB.
Thanks anyway.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
