Home » SQL & PL/SQL » SQL & PL/SQL » Two tricky forms/SQL questions
Two tricky forms/SQL questions [message #19024] Tue, 26 February 2002 08:08 Go to next message
Greg Horton
Messages: 37
Registered: February 2002
Member
Im really stuck on the following 2 points and would really appreciate some feedback :-)

The code below is for a cursor that im using. What i want it to do is use the value stored in :mine_data.table_name_list as the table name.
When ive tried to code it, as shown below but commented out, it comes back with error 103. Can you see what im doing wrong?

DECLARE
CURSOR c_frequent_items IS
SELECT distinct honours_points, count(*) count_star
from students /*:mine_data.table_name_list*/
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;

The second thing that im confused with is the count(*) function. I have the following piece of code below, and when executed id like to assign the count(*) value of distinct honours_points into a different variable. Ive attempted to answer my own question using the code below:

DECLARE
v_records_found NUMBER;
--
BEGIN
select count(*) distinct honours_points
into v_records_found
from students
group by honours_points
having count(*) >= :mine_data.support_value_text;
--
go_block('frequent_items');
--
:frequent_items.records_found_text:=v_records_found;
--
END;

The form complains about the reserved word distinct appearing after the count(*). Can you suggest where i may have gone wrong?

Thank you..

Greg
Re: Two tricky forms/SQL questions [message #19029 is a reply to message #19024] Tue, 26 February 2002 10:18 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
I've got to go back and reread your first question, but as for the second, try
SELECT COUNT(DISTINCT(HONOURS_POINTS)) syntax.
Re: Two tricky forms/SQL questions [message #19045 is a reply to message #19029] Tue, 26 February 2002 23:58 Go to previous messageGo to next message
Greg Horton
Messages: 37
Registered: February 2002
Member
Thanx for your reply Jon - much appreciated :-)

I tried to use the syntax that you suggested..

SELECT COUNT(DISTINCT(HONOURS_POINTS))

..but all of the values that satisfy the select statement are output to the screen, not the number of items returned. I think count(*) is needed, but im not sure how to implement it.
Re: Two tricky forms/SQL questions [message #19062 is a reply to message #19045] Wed, 27 February 2002 03:37 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
Please provide some sample data and what you would expect the output to be.
Previous Topic: Assigning the value of count(*) to a variable?
Next Topic: SUM A COLUM GROUPED BY ANOTHER
Goto Forum:
  


Current Time: Thu Mar 28 19:52:30 CDT 2024