| Author |
 Topic  |
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
 Posted - 07/18/2012 : 13:53:28
|
I have a data file as below
I have imported this data to a Raw table with Id as identity and Raw_data containing all the above
Table->Raw_tab(Id,Raw_data)
Table->Normal(Path,File,Level,Act,Status,Date,Time)--all varchar columns here
I want the output to be as in the Normal table, I have liberty to clean it in any either by script or by writing sp, tmp table/cursor etc anything will do, Below first line represents the column name of target( Normal) table
Path File Level Act status Date Time
---------------------------------------------------------------------
Path: /usr/lib/kpc Ora14.sdk 1.4.2.20 COMMIT COMPLETE 01/25/12 13:02:59
Path: /usr/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
.
.
.
Path: /usr2/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
.
.
-Neil |
Edited by - aakcse on 08/07/2012 21:18:20
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 07/18/2012 : 15:55:09
|
Are you sure you can't perform the parsing as part of the initial load from the file to the raw table? What is the format of the file? typically it is saved as "delimited" or "fixed width". Looks like it may be fixed width.
Be One with the Optimizer
TG |
 |
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/19/2012 : 03:53:07
|
It is space delimited, with multiple spaces, I can convert it to single space. as the data is not having spaces in it
-Neil |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/19/2012 : 04:40:33
|
Cursor is taking lot of time for this..
-Neil |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/19/2012 : 05:16:46
|
ID is it an identity ?
ID = 1 will contain the Path line
ID = 2, 4, 6 etc, even number will be the fileline
ID = 3, 5, 7 etc, will be the Level, Act, Status
select e.Raw_data, o.Raw_data
from Raw_tab e
inner join Raw_tab o on e.ID = o.ID - 1
where e.ID % 2 = 0
then just do some string parsing on the e.Raw_data and o.Raw_data to extract the required information
KH
Time is always against us
|
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/19/2012 : 06:50:36
|
Yes odd even would have worked here only if the path hav not come in the file
-Neil |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/19/2012 : 08:41:31
|
quote:
Originally posted by aakcse
Yes odd even would have worked here only if the path hav not come in the file
-Neil
It will still works. The INNER JOIN "on e.ID = o.ID - 1" and the WHERE "e.ID % 2 = 0" would have excluded ID = 1
You only need another query (or sub query to get the Path line)
select Path = (select Raw_data from Raw_tab where ID = 1),
e.Raw_data, o.Raw_data
from Raw_tab e
inner join Raw_tab o on e.ID = o.ID - 1
where e.ID % 2 = 0
KH
Time is always against us
|
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/19/2012 : 15:25:34
|
Thanks KH, if you see the data the path is coming multiple times and it can come at any line number, which might disturb the odd even combination
-Neil |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 08/05/2012 : 15:43:59
|
hmm now here came the challenge to me.. few records are as below
File Level Act Status Date Time
----------------------------------------------------------------------------
Path: /usr/lib/kpc
Ora14.sdk
1.4.2.20 COMMIT COMPLETE 01/25/12 13:02:59
Tivoli.rte
3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
Commit Complete are common in all these rows which are coming back 2 back, I didnt find more than this level like 3 rows below Tivoli.rte
there are very few records which have rows as above in entire file.
-Neil |
Edited by - aakcse on 08/05/2012 15:45:08 |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 08/05/2012 : 15:55:12
|
out put for the second row should be of as below
Path: /usr/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
Path: /usr/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
-Neil |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 08/05/2012 : 19:23:01
|
We can split this into 3 tables one with Path other with headers like 'Path: /usr/lib/kpc' and last one containing all commit comment lines.. commit & Comment is present in all lines except the one which has headers and path, hence this can be used in where clause...
now could anyone help me in joining these 3 tables and getting the data as below
Path: /usr/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
Path: /usr/lib/kpc Tivoli.rte 3.7.1.0 COMMIT COMPLETE 01/25/12 12:32:14
-Neil |
Edited by - aakcse on 08/05/2012 19:34:53 |
|
|
| |
Topic |
|
dataissue(Resolved)
