| To find greatest in a row [message #2988] |
Wed, 28 August 2002 07:49  |
Victoria
Messages: 152
Registered: July 2002
|
Senior Member |
|
|
Hi,
I have one cursor fetching 100 columns per row.
all are same datatype.
Now i want to find the greatest & least value in the fetched columns for each row.....
Can any one tell me the simplest way to do that???
Thanks in advance
Victoria
|
|
|
|
| Re: To find greatest in a row [message #2996 is a reply to message #2988] |
Wed, 28 August 2002 13:13  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Not pretty but...
for r in (select * from t) loop
v_least := least(r.col1, r.col2, ..., r.col100);
v_greatest := greatest(r.col1, r.col2, ..., r.col100);
end loop; |
|
|
|
| Re: To find greatest in a row [message #3038 is a reply to message #2988] |
Sun, 01 September 2002 21:24 |
Gilbey
Messages: 87
Registered: March 2002
|
Member |
|
|
Try this.......
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure leastgreatest
2 as
3 cursor cur_p is select * from test;
4 v_least number;
5 v_greatest number;
6 begin
7 for r in cur_p
8 loop
9 exit when cur_p%notfound;
10 v_least:=least(r.col1,r.col2,r.col3,r.col4,r.col5);
11 v_greatest:=greatest(r.col1,r.col2,r.col3,r.col4,r.col5);
12 dbms_output.put_line('The least value of is ->'||v_least);
13 dbms_output.put_line('The greatest value is ->'||v_greatest);
14 end loop;
15* end;
SQL> /
Procedure created.
SQL> exec leastgreatest;
The least value of is ->1
The greatest value is ->5
The least value of is ->1
The greatest value is ->5
The least value of is ->11
The greatest value is ->55
The least value of is ->11
The greatest value is ->55
The least value of is ->111
The greatest value is ->555
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
