PIVOT query 1

[fosa]

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

 

[fosa]

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

 

[SantaMufasa]

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?

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.”

 

[SantaMufasa]

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

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.”

 

[dstxaix]

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.

 

[fosa]

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

 

[SantaMufasa]

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:

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.

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.”

 

[fosa]

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,

 

[SantaMufasa]

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.):

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.


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.”

 

[fosa]

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