Home » RDBMS Server » Server Utilities » SQL*Loader use field from previous record
SQL*Loader use field from previous record [message #383412] Wed, 28 January 2009 16:10 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I have an input file that looks something like this

EM05AITR	AI-TYPE	A		AOF / SUBSURFACE PRESSURE
	AI-TYPE	B		BENZINE
	AI-TYPE	F		RESERVOIR FLUID
	AI-TYPE	G		GAS
	AI-TYPE	K		MERCURY INJECT CURVES
	AI-TYPE	L		LITHOLOGIC DESCRIPTION
	AI-TYPE	M		MICROBIOLOGICAL SURVEY
	AI-TYPE	O		OIL
	AI-TYPE	P		POTASH
	AI-TYPE	W		WATER
	AI-TYPE	Y		CORE-GAMMA RAY CORRELAT
				
EM05CITR	CI-CORE-TYPE	1		DIAMOND
	CI-CORE-TYPE	2		WIRELINE
	CI-CORE-TYPE	3		SIDEWALL
	CI-CORE-TYPE	4		ASSUMED DIAMOND
	CI-CORE-TYPE	5		ASSUMED WIRELINE
				
	CI-ANALYZED	N		NOT ANALYSED
	CI-ANALYZED	Y		ANALYSED
	CI-ANALYZED	S		SPECIAL CORE ANALYSE


And I'd like SQL*Loader to be able to use the last valid entry for the first column until it gets another valid entry. Is this possible or is some sort of [pre/post]processing going to be necessary?

I'd like the above to be loaded as the following
EM05AITR	AI-ORIGIN	B	BATTERY
EM05AITR	AI-ORIGIN	C	CORE
EM05AITR	AI-ORIGIN	D	DST
EM05AITR	AI-ORIGIN	L	LOGS
EM05AITR	AI-ORIGIN	P	PERFORATIONS
EM05AITR	AI-ORIGIN	R	REPEAT FORMATION TESTER
EM05AITR	AI-ORIGIN	S	SEPARATOR
EM05AITR	AI-ORIGIN	T	TREATER
EM05AITR	AI-ORIGIN	W	WELLHEAD
EM05AITR	AI-ORIGIN	X	SAMPLE
EM05CITR	CI-ANALYZED	N	NOT ANALYSED
EM05CITR	CI-ANALYZED	S	SPECIAL CORE ANALYSE
EM05CITR	CI-ANALYZED	Y	ANALYSED
EM05CITR	CI-CORE-TYPE	1	DIAMOND
EM05CITR	CI-CORE-TYPE	2	WIRELINE
EM05CITR	CI-CORE-TYPE	3	SIDEWALL
EM05CITR	CI-CORE-TYPE	4	ASSUMED DIAMOND
EM05CITR	CI-CORE-TYPE	5	ASSUMED WIRELINE


Re: SQL*Loader use field from previous record [message #383515 is a reply to message #383412] Thu, 29 January 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does "AI-ORIGIN" come from?
Anyway, I will use "external table" in this case.

Regards
Michel
Re: SQL*Loader use field from previous record [message #383650 is a reply to message #383515] Thu, 29 January 2009 08:20 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
More than that, where does BATTERY and CORE come from, not to mention most of the data.

I guess you can write a random word generator to create data in the table that is not in the original file.
Re: SQL*Loader use field from previous record [message #383676 is a reply to message #383412] Thu, 29 January 2009 11:19 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
My bad, I thought I was copying and pasting the proper sections but apparently my brain was on holiday, here is the corrected version of what I would like to have loaded from the raw data - (where did my edit button go?)

EM05AITR	AI-TYPE	A	AOF / SUBSURFACE PRESSURE
EM05AITR	AI-TYPE	B	BENZINE
EM05AITR	AI-TYPE	F	RESERVOIR FLUID
EM05AITR	AI-TYPE	G	GAS
EM05AITR	AI-TYPE	K	MERCURY INJECT CURVES
EM05AITR	AI-TYPE	L	LITHOLOGIC DESCRIPTION
EM05AITR	AI-TYPE	M	MICROBIOLOGICAL SURVEY
EM05AITR	AI-TYPE	O	OIL
EM05AITR	AI-TYPE	P	POTASH
EM05AITR	AI-TYPE	W	WATER
EM05AITR	AI-TYPE	Y	CORE-GAMMA RAY CORRELAT
EM05CITR	CI-CORE-TYPE	1	DIAMOND
EM05CITR	CI-CORE-TYPE	2	WIRELINE
EM05CITR	CI-CORE-TYPE	3	SIDEWALL
EM05CITR	CI-CORE-TYPE	4	ASSUMED DIAMOND
EM05CITR	CI-CORE-TYPE	5	ASSUMED WIRELINE
EM05CITR	CI-ANALYZED	N	NOT ANALYSED
EM05CITR	CI-ANALYZED	S	SPECIAL CORE ANALYSE
EM05CITR	CI-ANALYZED	Y	ANALYSED


I am familiar with external tables, but not entirely how they will be helpful in this case.
Re: SQL*Loader use field from previous record [message #383679 is a reply to message #383676] Thu, 29 January 2009 11:56 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With external table it is no more a question of loader, it is a question of SQL.
Just map each field of your file to a column to an external table and then do SQL.

Regards
Michel
Previous Topic: import / Load SQL Server 2000 Data inTO Oracle 8
Next Topic: Invoke SQL Loader
Goto Forum:
  


Current Time: Mon Dec 05 13:10:28 CST 2016

Total time taken to generate the page: 0.11398 seconds