| Assigning the value of count(*) to a variable? [message #37759] |
Wed, 27 February 2002 00:25  |
Greg Horton
Messages: 37
Registered: February 2002
|
Member |
|
|
When a select statement has been exectuted, after the results have been displayed to screen the number of rows returned is displayed, for example:
2000 rows selected.
Does anyone know how i can capture this value if im already using the statement and cursor below to retrieve data?
DECLARE
CURSOR c_frequent_items IS
SELECT distinct honours_points, count(*) count_star
from students
group by honours_points
having count(*) >= :mine_data.support_value_text;
--
BEGIN
go_block('frequent_items');
FOR cursor_rec IN c_frequent_items
LOOP
create_record;
:frequent_items.honours_points:=cursor_rec.honours_points;
:frequent_items.count_star:=cursor_rec.count_star;
END LOOP;
END;
Using the command
SELECT COUNT(*) DISTINCT HONOURS_POINTS..
doesn't work.
|
|
|
|
| Re: Assigning the value of count(*) to a variable? [message #37762 is a reply to message #37759] |
Wed, 27 February 2002 01:17  |
ajay
Messages: 45
Registered: December 2000
|
Member |
|
|
hi friend
Here you are using distinct with group by caluse,so when you are using group by clause it automatically takes distinct valuse.
the best i can suggest is , use group by clause rather using distinct .It (distinct) always considered when you are not using some function in your's select comamand
|
|
|
|
| Re: Assigning the value of count(*) to a variable? [message #37765 is a reply to message #37759] |
Wed, 27 February 2002 01:59 |
Greg Horton
Messages: 37
Registered: February 2002
|
Member |
|
|
Thanks for your response Ajay. I tried what u suggested but didnt get the results that i required.
Below is the now working code:
DECLARE
total_record number:=0;
CURSOR c_frequent_items IS
SELECT distinct honours_points, count(*) count_star
from students
group by honours_points
having count(*) >= :mine_data.support_value_text;
--
BEGIN
go_block('frequent_items');
FOR cursor_rec IN c_frequent_items
LOOP
create_record;
total_record:=total_record+1;
:frequent_items.honours_points:=cursor_rec.honours_points;
:frequent_items.count_star:=cursor_rec.count_star;
END LOOP;
:frequent_items.row_count:=total_record;
END;
Thanks again.
Greg
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
