OraFAQ Forum: SQL & PL/SQL » Lag Function problems pls help

Home » SQL & PL/SQL » SQL & PL/SQL » Lag Function problems pls help

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

Lag Function problems pls help [message #596654]

Wed, 25 September 2013 08:05

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

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

Attachment: lagFunction_help.txt
(Size: 1.18KB, Downloaded 1375 times)

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

Report message to a moderator

Re: Lag Function problems pls help [message #596657 is a reply to message #596654]

Wed, 25 September 2013 08:16

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

Report message to a moderator

Re: Lag Function problems pls help [message #596658 is a reply to message #596654]

Wed, 25 September 2013 08:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How can you tell that collection ID "SMRE11" belongs to QU "Blade: qcusrea11 / NOB1"?

MHE

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 16:07:58 CDT 2024