| Sort data output to excel (owa_util) from 2 cursor [message #609346] |
Wed, 05 March 2014 20:47  |
aryanna
Messages: 44
Registered: July 2007
|
Member |
|
|
Hi,
I'm output my data to excel using owa_util. How I can sort/order the data if it from 2 different cursor.
I need to sort by Y ascending and b,c descending in excel output. Any way to pass this excel sorting?
This is my sample code structure.
Cursor c_one
select x,y,x from table1
order by y;
cursor c_two(c_one_id number) is
select a,b,c from table2 where id=c_one_id
order by b,c desc;
Begin
owa_util.mime_header(ccontent_type => 'application/vnd.ms-excel');
htp.p('ID;ORACLE');
htp.p(';');
if rec_1 in c_one loop
htp.p('C;X1;K"'||rec_1.x||'"');
htp.p('C;X2;K"'||rec_1.y||'"');
if rec_2 in c_two loop
htp.p('C;X3;K"'||rec_2.a||'"');
htp.p('C;X4;K"'||rec_2.b||'"');
end loop;
htp.p('E');
end loop;
End;
The output only sort by column y.
Please help.
|
|
|
|
|
|
| Re: Sort data output to excel (owa_util) from 2 cursor [message #617376 is a reply to message #609346] |
Fri, 27 June 2014 18:03  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your code is full of errors.
You need to pass a value from one cursor to the other like so:
for rec_2 in c_two(rec_1.x) loop
The above assumes that x is the value that you want to pass from c_one to match the id in c_two.
It is kind of a confusing mess where you are selecting some values, ordering by other values, and displaying other values, so you may not be looking at the right values when checking the ordering.
When you say:
order by b,c desc
that means
order by b asc, c desc
I suspect that you may be looking for
order by b desc, c desc
Please see the following example that demonstrates proper code logic and ordering
SCOTT@orcl12c> create table table1
2 (x number,
3 y number,
4 z number)
5 /
Table created.
SCOTT@orcl12c> insert all
2 into table1 values (1, 2, 3)
3 into table1 values (4, 5, 6)
4 select * from dual
5 /
2 rows created.
SCOTT@orcl12c> create table table2
2 (id number,
3 a number,
4 b number,
5 c number)
6 /
Table created.
SCOTT@orcl12c> insert all
2 into table2 values (1, 10, 20, 30)
3 into table2 values (1, 10, 30, 70)
4 into table2 values (4, 40, 50, 60)
5 into table2 values (4, 40, 60, 80)
6 select * from dual
7 /
4 rows created.
SCOTT@orcl12c> declare
2 Cursor c_one is
3 select x,y,z from table1 order by y;
4 cursor c_two (c_one_id number) is
5 select a,b,c from table2 where id=c_one_id order by b desc, c desc;
6 Begin
7 dbms_output.put_line ('------------------------');
8 for rec_1 in c_one loop
9 dbms_output.put_line('y: '||rec_1.y);
10 for rec_2 in c_two(rec_1.x) loop
11 dbms_output.put_line('b: '||rec_2.b);
12 end loop;
13 dbms_output.put_line ('------------------------');
14 end loop;
15 End;
16 /
------------------------
y: 2
b: 30
b: 20
------------------------
y: 5
b: 60
b: 50
------------------------
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
