Home » SQL & PL/SQL » SQL & PL/SQL » How to convert the data?
How to convert the data? [message #195425] Thu, 28 September 2006 09:36 Go to next message
gowthamsen
Messages: 14
Registered: August 2006
Junior Member
Hi,

I have one file which contains the data as follows.
fieds: f1,f2,f3

f1 f2 f3
---------
1 2 4
...3.... (here concider '.' as space)
w x z
...y.... (here concider '.' as space--space is not recongnizing while previewing the message. so I used '.' for indicating the f1 and f3 are null values for the rows 2 and 4.)

The above table is retrieved using an external table. The fileformat is changed unknownly.(Actual data is as followed way).

I would like to retrieve the output(recollect data to the same format) as follows.

f1 f2 f3 f4
-----------
1 2 3 4
w x y z

I am trying to find. But I couldn't find any solution.

Could anyone please suggest me.

Thanks and regards
Gowtham Sen.

Re: How to convert the data? [message #195442 is a reply to message #195425] Thu, 28 September 2006 11:38 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Here the very inner select would be your external table:

sql>select *
  2    from (select f1, f2, lead(f2) over (order by null) f3, f3 f4
  3            from (select '1' f1, '2' f2, '4' f3 from dual
  4                  union all
  5                  select null, '3', null from dual
  6                  union all
  7                  select 'w', 'x', 'z' from dual
  8                  union all
  9                  select null, 'y', null from dual))
 10   where f1 is not null;

F1 F2 F3 F4
-- -- -- --
1  2  3  4
w  x  y  z

2 rows selected.
Re: How to convert the data? [message #195443 is a reply to message #195425] Thu, 28 September 2006 11:39 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You need to define more clearly what the problem is. We can obviously see that "1 2 3 4" and "w x y z" are a sequence of numbers and a sequence of letters. But what is to stop Oracle combining these as:

1 2 y 4
w x 3 z

i.e. what are your rules for determining which rows are combined with which other rows. I think we would need a bit more information that this very theoretical and limited picture of your data.
Re: How to convert the data? [message #195503 is a reply to message #195442] Fri, 29 September 2006 01:51 Go to previous messageGo to next message
gowthamsen
Messages: 14
Registered: August 2006
Junior Member
Thank you Todd Barry.

It helps me.

>>Cthulhu: what are your rules for determining which rows are combined with which other rows. I think we would need a bit more information that this very theoretical and limited picture of your data.

The row which are below to the main row (1 2 4) I want to add it to main row.

Thanks and regards
Gowtham sen.
Re: How to convert the data? [message #195508 is a reply to message #195503] Fri, 29 September 2006 02:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What the squamous and tentacular @Cthulhu is trying to clarify is:

How do you know which row goes after which other row. There is no order inherrent in the results of a query unless an ORDER BY statement is used, and there is nothing in your data that you can order by.
Re: How to convert the data? [message #195512 is a reply to message #195508] Fri, 29 September 2006 02:30 Go to previous messageGo to next message
gowthamsen
Messages: 14
Registered: August 2006
Junior Member
Thank you JRowBottom for pointing me regarding this.

Yes, you are right. Then at this case, how we can solve this?

Thanks and regards
Gowtham Sen.
Re: How to convert the data? [message #195516 is a reply to message #195512] Fri, 29 September 2006 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I'd look at correcting the Sqlldr details for the external table.

You can use the Concatenate command to join two rows in the external table together.
Re: How to convert the data? [message #195605 is a reply to message #195516] Fri, 29 September 2006 12:17 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
My response is predicated on the fact that this is an external table we are talking about, and there is a "default" order with external tables - unlike "normal" tables.
Re: How to convert the data? [message #195686 is a reply to message #195425] Sun, 01 October 2006 03:48 Go to previous messageGo to next message
gowthamsen
Messages: 14
Registered: August 2006
Junior Member
Yes you are right Todd Barry.

In external table, the order is same. By all of our discussion again a question occur in my mind.
Suppose if there are more than record to be considered. In the sense if the data is as follows,

f1 f2 f3
---------
1 2 5
...3.... (here concider '.' as space)
...4....
w x m
...y....
...z....
(here concider '.' as space--space is not recongnizing
while previewing the message. so I used '.' for indicating the f1 and f3 are null values for the rows 2 and 4.)
output
-------
f1 f2 f3 f4 f5
---------------
1 2 3 4 5
w x y z m

Then how could we solve such type of problem.

Any suggesitions are welcome.

Thanks and regards
Gowtham sen.
Re: How to convert the data? [message #195828 is a reply to message #195686] Mon, 02 October 2006 12:05 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Just use the offset parameter in the LEAD function:

sql>with t as
  2    (select '1' f1, '2' f2, '5' f3
  3       from dual
  4     union all
  5     select null, '3', null
  6       from dual
  7     union all
  8     select null, '4', null
  9       from dual
 10     union all
 11     select 'w', 'x', 'm'
 12       from dual
 13     union all
 14     select null, 'y', null
 15       from dual
 16     union all
 17     select null, 'z', null
 18       from dual)
 19  select *
 20    from (select f1, f2, 
 21                 lead(f2, 1) over (order by null) as f3,
 22                 lead(f2, 2) over (order by null) as f4,
 23                 f3 as f5
 24            from t)
 25   where f1 is not null;

F1 F2 F3 F4 F5
-- -- -- -- --
1  2  3  4  5
w  x  y  z  m

2 rows selected.
Previous Topic: Optimisation problem
Next Topic: What's the difference beetwen v$sql, v$sqltext and v$sqlarea ?
Goto Forum:
  


Current Time: Sat Dec 03 04:12:53 CST 2016

Total time taken to generate the page: 0.26486 seconds