Home » SQL & PL/SQL » SQL & PL/SQL » Lag Function problems pls help
Lag Function problems pls help [message #596654] Wed, 25 September 2013 08:05 Go to next message
Messages: 39
Registered: October 2011
Location: GA
I have uploaded the txt file since format is not fitting here.

I have a table called xyz and it has two colums called QU and collectionID
The data is stored in the table is like Scenario 1 but I want to extract the data to look like Scenario 2

Scenario 1

QU	                        	CollectionID

Blade: qcusrea11 / NOB1,                 null
null,	                          	SMRE11		
null,	 				SMRE21		
null,	 				SMRE31		
null,	 				SMRE41		
Blade: qcusrea12 / NOB2                 null
null	 				SMRE11		
null	 				SMRE21		
null	 				SMRE31		
null	 				SMRE41		
Blade: qcusred11 / ADC1                 null
null	 				SMREM1		
null	 				SMREN1		
null	 				SMREP1		
null	 				SMREQ1		

Scenario 2	
QU					CollectionID	
Blade: qcusrea11 / NOB1	 		SMRE11		
Blade: qcusrea11 / NOB1  		SMRE21		
Blade: qcusrea11 / NOB1  		SMRE31		
Blade: qcusrea11 / NOB1	 		SMRE41		
Blade: qcusrea12 / NOB2	 		SMRE11		
Blade: qcusrea12 / NOB2	 		SMRE21		
Blade: qcusrea12 / NOB2	 		SMRE31		
Blade: qcusrea12 / NOB2	 		SMRE41		
Blade: qcusred11 / ADC1	 		SMREM1		
Blade: qcusred11 / ADC1	 		SMREN1		
Blade: qcusred11 / ADC1	 		SMREP1		
Blade: qcusred11 / ADC1	 		SMREQ1	

[Updated on: Wed, 25 September 2013 08:19] by Moderator

Report message to a moderator

icon13.gif  Re: Lag Function problems pls help [message #596657 is a reply to message #596654] Wed, 25 September 2013 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65085
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Data in a table are stored with no order so saying the data are the way you say or are:
Blade: qcusrea11 / NOB1, null
Blade: qcusrea12 / NOB2 null
Blade: qcusred11 / ADC1 null
null, SMRE31
null, SMRE41
null SMRE11
null SMRE21
null, SMRE11
null, SMRE21
null SMRE31
null SMRE41
null SMREM1
null SMREN1
null SMREP1
null SMREQ1
are equally true.

If you have no other column that indicates an order, you can't do what you want.

You ave to restart the loading processing from your file and set the column during the loading. I'd use an external table for this as SQL is far easier to manager than SQL*Loader but there are very skilled people that will able to answer on this latter tool.
Re: Lag Function problems pls help [message #596658 is a reply to message #596654] Wed, 25 September 2013 08:17 Go to previous message
Messages: 7062
Registered: December 2001
Senior Member
How can you tell that collection ID "SMRE11" belongs to QU "Blade: qcusrea11 / NOB1"?

Previous Topic: using a cursor to fetch data
Next Topic: how to write LAST_INSERT_ID() in oracle
Goto Forum:

Current Time: Mon Jul 24 12:00:26 CDT 2017

Total time taken to generate the page: 0.12440 seconds