Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

PIVOT query 1

Status
Not open for further replies.
fosa

fosa

IS-IT--Management
Mar 18, 2008
26
FR
Hi all,

I have datas like this , they come from a 3 party tool
not from an oracle table

15/09/2008 10:00:00:15/09/2008 10:01:00:15/09/2008 10:02:00:15/09/2008 10:03:00:15/09/2008 10:04:00:15/09/2008 10:05:00
44.22:44.2:44.165:44.175:44.17:44.2
44.24:44.21:44.2:44.19:44.235:44.21
44.2:44.16:44.155:44.15:44.165:44.18
44.21:44.175:44.17:44.185:44.2:44.18
14566:6520:15562:14410:33259:8652


I want to have this
TIME OPEN HIGH LOW CLOSE VOLUME

15/09/2008 10:00 44.165 44.175 44.17 44.2 14566
15/09/2008 10:01 44.2 44.19 44.235 44.21 6520
15/09/2008 10:02 44.155 44.15 44.165 44.18 15562
15/09/2008 10:03 44.17 44.185 44.2 44.18 14410
15/09/2008 10:04 15562 14410 33259 8652 33259


HOw can I deal with It ?

I am under 10G R1

Thanks in advance
 
Sort by date Sort by votes
The result I want is

TIME OPEN HIGH LOW CLOSE VOLUME

15/09/2008 10:00 44.22 44.24 44.2 44.21 14566
15/09/2008 10:01 44.2 44.21 44.16 44.175 6520
15/09/2008 10:02 44.165 44.2 44.155 44.17 15562
15/09/2008 10:03 44.175 44.19 44.15 44.185 14410
15/09/2008 10:04 44.17 44.235 44.165 44.2 33259
15/09/2008 10:05 44.2 44.21 44.18 44.18 8652

and not the result in the last past


 
Upvote 0 Downvote
Fosa,

Do your data come always in 6-minute time intervals? Or are the intervals variable in number? Are you allowed to create PL/SQL user-defined procedures and functions?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
...And are the Date/Time components always 19 characters separated by ":"?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
I understand this question is asked in Oracle forum. But, I thought I will anyway suggest that data formatting be done before loading into a database. An awk/Perl script or a C or Java program or VB.NET (depending on operating system) can do the transformation.
 
Upvote 0 Downvote
HI all,

Do your data come always in 6-minute time intervals? Or are the intervals variable in number?

The interval is minute , it is in 6 minutes here as an example, in reality it is from a day work like 8:00:00 to 19:00:00, and we try to get minutelly datas.


Are you allowed to create PL/SQL user-defined procedures and functions?

I am allowed to create function,procedure, package...

.And are the Date/Time components always 19 characters separated by ":"?

yes,always

Thanks
 
Upvote 0 Downvote
Fosa,

Given your above specifications (for a variable number of entries per row based upon the number of time periods on the header record per download), here is code that should do what you wanted:
Code: 
select * from quotes_in;

QUOTE_TXT
------------------------------------------------------------------------------------------------------------------------------------
15/09/2008 10:00:00:15/09/2008 10:01:00:15/09/2008 10:02:00:15/09/2008 10:03:00:15/09/2008 10:04:00:15/09/2008 10:05:00
44.22:44.2:44.165:44.175:44.17:44.2
44.24:44.21:44.2:44.19:44.235:44.21
44.2:44.16:44.155:44.15:44.165:44.18
44.21:44.175:44.17:44.185:44.2:44.18
14566:6520:15562:14410:33259:8652
15/09/2008 11:00:00:15/09/2008 11:01:00:15/09/2008 11:02:00:15/09/2008 11:03:00:15/09/2008 11:04:00:15/09/2008 11:05:00:15/09/2008 11:06:00 (<-- Sorry, because this row has 7 dates/times, it was too long to display on one line...it wrapped onto the next line.)
44.32:44.3:44.165:44.175:44.17:44.3:44.35
44.34:44.31:44.3:44.19:44.335:44.31:44.36
44.3:44.16:44.155:44.15:44.165:44.18:44.20
44.31:44.175:44.17:44.185:44.3:44.18:44.23
14566:6520:15562:14410:33259:8652:10999

declare
    rec_type number := 0;
    type nums is table of number index by binary_integer;
    beg_loc nums;
    len nums;
    curr_delim_loc nums;
    next_delim_loc nums;
    hold_quote quotes_in.quote_txt%type;
    num_entries number;
    type y is record (a quotes%rowtype);
    type x is table of y index by binary_integer;
    z x;
    function get_curr_num (str_in varchar2,which_num number) return number is
        begin
            curr_delim_loc(which_num) := instr(str_in,':',1,which_num);
            next_delim_loc(which_num) := instr(str_in,':',1,which_num+1);
            return to_number(substr(str_in,curr_delim_loc(which_num)+1,next_delim_loc(which_num)-curr_delim_loc(which_num)-1));
        end;
    procedure insert_rows is
        begin
            for i in 1..num_entries loop
                insert into quotes values(z(i).a.time,z(i).a.open,z(i).a.high,z(i).a.low,z(i).a.close,z(i).a.volume);
            end loop;
        end;
begin
    for r in (select * from quotes_in) loop
        rec_type := rec_type + 1;
        hold_quote := ':'||r.quote_txt||':';
        if rec_type = 1 then
             num_entries := length(hold_quote) - length(replace(hold_quote,' ',null));
             for i in 1..num_entries loop
                  z(i).a.time := to_date(substr(hold_quote,((i-1)*20)+2,19),'dd/mm/yyyy hh24:mi:ss');
             end loop;
        elsif rec_type = 2 then
             for i in 1..num_entries loop
                  z(i).a.open := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 3 then
             for i in 1..num_entries loop
                  z(i).a.high := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 4 then
             for i in 1..num_entries loop
                  z(i).a.low := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 5 then
             for i in 1..num_entries loop
                  z(i).a.close := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 6 then
             for i in 1..num_entries loop
                  z(i).a.volume := get_curr_num(hold_quote,i);
             end loop;
             rec_type := 0;
             Insert_rows;
        end if;
    end loop;
    commit;
end;
/

select to_char(time,'dd/mm/yyyy hh24:mi:ss')time,open,high,low,close,volume from quotes;

TIME                      OPEN       HIGH        LOW      CLOSE     VOLUME
------------------- ---------- ---------- ---------- ---------- ----------
15/09/2008 10:00:00      44.22      44.24       44.2      44.21      14566
15/09/2008 10:01:00       44.2      44.21      44.16     44.175       6520
15/09/2008 10:02:00     44.165       44.2     44.155      44.17      15562
15/09/2008 10:03:00     44.175      44.19      44.15     44.185      14410
15/09/2008 10:04:00      44.17     44.235     44.165       44.2      33259
15/09/2008 10:05:00       44.2      44.21      44.18      44.18       8652
15/09/2008 11:00:00      44.32      44.34       44.3      44.31      14566
15/09/2008 11:01:00       44.3      44.31      44.16     44.175       6520
15/09/2008 11:02:00     44.165       44.3     44.155      44.17      15562
15/09/2008 11:03:00     44.175      44.19      44.15     44.185      14410
15/09/2008 11:04:00      44.17     44.335     44.165       44.3      33259
15/09/2008 11:05:00       44.3      44.31      44.18      44.18       8652
15/09/2008 11:06:00      44.35      44.36       44.2      44.23      10999

13 rows selected.
(i.e., 6 rows for the first time period and 7 rows for the second time period.)
Let us know if this is suitable or if you have questions.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Hi,

Thanks very much for this reply

but I have 2 problemS
the RAW datas are separated by "|" not by carriage return
so it in fact like this
15/09/2008 10:00:00:15/09/2008 10:01:00:15/09/2008 10:02:00:15/09/2008 10:03:00:15/09/2008 10:04:00|
44.22:44.2:44.165:44.175:44.173.735:43.71|44.24:44.21:44.2:44.19:44.235:44.21

the second problem is the size of raw datas , as it comes from 3 party tool I have to put in an Oracle variable
wich is beyond the capacity of varchar2(32000) if I use
an interval from 08:00 to 19:00

How can I turn your script to deal with it ?

Sorry for this changement but I was in the logic of doing this

select time into vtime;
select open into v_open;
...



Best regards,



 
Upvote 0 Downvote
Fosa,

You must be the luckiest guy alive...Your quote feed, being one continuous string of data, is a perfect application of Oracle's External Tables.

Without my having to change even one character of my above PL/SQL code, I simply define your single-string ASCII data file as an Oracle External table. (I will not post the contents of my flat ASCII equivalent of your continuous-string data since it causes the Tek-Tips screen to go beyond the right margin and to make the data beyond the right margin no longer visible on many web browsers.):
Code: 
drop table quotes_in
/
create table quotes_in (quote_txt varchar2(1000))
organization external
(  type oracle_loader
   default directory TTFlatFiles
   access parameters
   (records delimited by '|'
   )
location ('quotes_in.txt')
)
reject limit unlimited;

Table created.

select * from quotes_in;

QUOTE_TXT
-------------------------------------------------------------------------------------------------------------------------------------------
15/09/2008 10:00:00:15/09/2008 10:01:00:15/09/2008 10:02:00:15/09/2008 10:03:00:15/09/2008 10:04:00:15/09/2008 10:05:00
44.22:44.2:44.165:44.175:44.17:44.2
44.24:44.21:44.2:44.19:44.235:44.21
44.2:44.16:44.155:44.15:44.165:44.18
44.21:44.175:44.17:44.185:44.2:44.18
14566:6520:15562:14410:33259:8652
15/09/2008 11:00:00:15/09/2008 11:01:00:15/09/2008 11:02:00:15/09/2008 11:03:00:15/09/2008 11:04:00:15/09/2008 11:05:00:15/09/2008 11:06:00
44.32:44.3:44.165:44.175:44.17:44.3:44.35
44.34:44.31:44.3:44.19:44.335:44.31:44.36
44.3:44.16:44.155:44.15:44.165:44.18:44.20
44.31:44.175:44.17:44.185:44.3:44.18:44.23
14566:6520:15562:14410:33259:8652:10999

12 rows selected.

truncate table quotes;

Table truncated.

declare
    rec_type number := 0;
    type nums is table of number index by binary_integer;
    beg_loc nums;
    len nums;
    curr_delim_loc nums;
    next_delim_loc nums;
    hold_quote quotes_in.quote_txt%type;
    num_entries number;
    type y is record (a quotes%rowtype);
    type x is table of y index by binary_integer;
    z x;
    function get_curr_num (str_in varchar2,which_num number) return number is
        begin
            curr_delim_loc(which_num) := instr(str_in,':',1,which_num);
            next_delim_loc(which_num) := instr(str_in,':',1,which_num+1);
            return to_number(substr(str_in,curr_delim_loc(which_num)+1,next_delim_loc(which_num)-curr_delim_loc(which_num)-1));
        end;
    procedure insert_rows is
        begin
            for i in 1..num_entries loop
                insert into quotes values(z(i).a.time,z(i).a.open,z(i).a.high,z(i).a.low,z(i).a.close,z(i).a.volume);
            end loop;
        end;
begin
    for r in (select * from quotes_in) loop
        rec_type := rec_type + 1;
        hold_quote := ':'||r.quote_txt||':';
        if rec_type = 1 then
             num_entries := length(hold_quote) - length(replace(hold_quote,' ',null));
             for i in 1..num_entries loop
                  z(i).a.time := to_date(substr(hold_quote,((i-1)*20)+2,19),'dd/mm/yyyy hh24:mi:ss');
             end loop;
        elsif rec_type = 2 then
             for i in 1..num_entries loop
                  z(i).a.open := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 3 then
             for i in 1..num_entries loop
                  z(i).a.high := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 4 then
             for i in 1..num_entries loop
                  z(i).a.low := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 5 then
             for i in 1..num_entries loop
                  z(i).a.close := get_curr_num(hold_quote,i);
             end loop;
        elsif rec_type = 6 then
             for i in 1..num_entries loop
                  z(i).a.volume := get_curr_num(hold_quote,i);
             end loop;
             rec_type := 0;
             Insert_rows;
        end if;
    end loop;
    commit;
end;
/

PL/SQL procedure successfully completed.

select to_char(time,'dd/mm/yyyy hh24:mi:ss')time,open,high,low,close,volume from quotes;

Logon Date/Time           OPEN       HIGH        LOW      CLOSE     VOLUME
------------------- ---------- ---------- ---------- ---------- ----------
15/09/2008 10:00:00      44.22      44.24       44.2      44.21      14566
15/09/2008 10:01:00       44.2      44.21      44.16     44.175       6520
15/09/2008 10:02:00     44.165       44.2     44.155      44.17      15562
15/09/2008 10:03:00     44.175      44.19      44.15     44.185      14410
15/09/2008 10:04:00      44.17     44.235     44.165       44.2      33259
15/09/2008 10:05:00       44.2      44.21      44.18      44.18       8652
15/09/2008 11:00:00      44.32      44.34       44.3      44.31      14566
15/09/2008 11:01:00       44.3      44.31      44.16     44.175       6520
15/09/2008 11:02:00     44.165       44.3     44.155      44.17      15562
15/09/2008 11:03:00     44.175      44.19      44.15     44.185      14410
15/09/2008 11:04:00      44.17     44.335     44.165       44.3      33259
15/09/2008 11:05:00       44.3      44.31      44.18      44.18       8652
15/09/2008 11:06:00      44.35      44.36       44.2      44.23      10999

13 rows selected.
...And the beauty is that there is no 32K limit to your ASCII data feed size.

Let us know what you think of this.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Hi Mufasa,

Thanks very much, your response is really what I needed

In fact with Oracle, there are a lot of tools now, external table, pipelined, clob ...

and in 11g we have pivot

And last, your responses were done very quicky


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cabbey77
  • Locked
  • Question
Concat and/or Pivot?
Replies
3
Views
114
cabbey77
cabbey77
Andrzejek
  • Locked
  • Question
Pivot - NULL to 0 1
Replies
6
Views
315
Andrzejek
Andrzejek
ssmith999
  • Locked
  • Question
Query help w/ fiscal calendar query
Replies
1
Views
96
johnherman
johnherman
rk68
  • Locked
  • Question
auto execute procedure/query on record insert
Replies
13
Views
119
rk68
rk68
Ken
  • Locked
  • Question
How to make SQL sub query work in Oracle, getting error ?
Replies
4
Views
177
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top