OraFAQ Forum: Server Utilities » SQL*Loader use field from previous record

Home » RDBMS Server » Server Utilities » SQL*Loader use field from previous record

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

SQL*Loader use field from previous record [message #383412]

Wed, 28 January 2009 16:10

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

Report message to a moderator

Re: SQL*Loader use field from previous record [message #383515 is a reply to message #383412]

Thu, 29 January 2009 00:57

Michel Cadot
Messages: 68647
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

Report message to a moderator

Re: SQL*Loader use field from previous record [message #383650 is a reply to message #383515]

Thu, 29 January 2009 08:20

joy_division
Messages: 4963
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.

Report message to a moderator

Re: SQL*Loader use field from previous record [message #383676 is a reply to message #383412]

Thu, 29 January 2009 11:19

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.

Report message to a moderator

Re: SQL*Loader use field from previous record [message #383679 is a reply to message #383676]

Thu, 29 January 2009 11:56

Michel Cadot
Messages: 68647
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 12:34:07 CDT 2024