Home » SQL & PL/SQL » SQL & PL/SQL » Group function not allowed in a cursor?
Group function not allowed in a cursor? [message #35667] |
Thu, 04 October 2001 14:20 |
Mike
Messages: 417 Registered: September 1998
|
Senior Member |
|
|
Hey-
I have the following query, valid when run as an SQL statement:
Select Number, Communication_Date, Communication_Seq_Num, Communication_Type
from Communication
WHERE loan_number in (SElect number
from (SElect number, communication_date, count(number) as count
from Communication
where To_Char(Communication_Date, 'HH24:MI') <> '00:00' AND Update_Module = 'COLLECT_I'
group by number, communication_date)
where count > 1);
I can explain the purpose behind it, but it's not necessary for this question, I don't belive. When I try to place this in a cursor, and open the cursor, I get back the message: 'Group function is not allowed here'. Can you not do groups in cursors, or have I got some other problem by running this in a cursor? Unfortunately, I have to page through the results, so running it as a stand-alone query isn't really an option
Thanks
Mike
----------------------------------------------------------------------
|
|
|
Re: Group function not allowed in a cursor? [message #35677 is a reply to message #35667] |
Fri, 05 October 2001 00:39 |
Hans
Messages: 42 Registered: September 2000
|
Member |
|
|
the problem is the plsql reserved keyword count in your query
drop table communication;
create table communication (
loan_number number(10),
communication_date date,
Communication_Seq_Num number(5),
Communication_Type varchar2(10),
update_module varchar2(10)
);
select
loan_number,
Communication_Date,
Communication_Seq_Num,
Communication_Type
from Communication
where loan_number in
(
select loan_number from
(
select loan_number, communication_date, count(loan_number) as count from Communication
where To_Char(Communication_Date, 'HH24:MI') <> '00:00' AND Update_Module = 'COLLECT_I'
group by loan_number, communication_date
)
where count > 1
);
no rows selected
declare
cursor cur is
select
loan_number,
Communication_Date,
Communication_Seq_Num,
Communication_Type
from Communication
where loan_number in
(
select loan_number from
(
select loan_number, communication_date, count(loan_number) as count1 from Communication
where To_Char(Communication_Date, 'HH24:MI') <> '00:00' AND Update_Module = 'COLLECT_I'
group by loan_number, communication_date
)
where count1 > 1
);
rec cur%rowtype;
begin
open cur;
fetch cur into rec;
close cur;
end;
/
declare
*
ERROR at line 1:
ORA-00934: group function is not allowed here
ORA-06512: at line 3
ORA-06512: at line 22
declare
cursor cur is
select
loan_number,
Communication_Date,
Communication_Seq_Num,
Communication_Type
from Communication
where loan_number in
(
select loan_number from
(
select loan_number, communication_date, count(loan_number) as <b>count1</b> from Communication
where To_Char(Communication_Date, 'HH24:MI') <> '00:00' AND Update_Module = 'COLLECT_I'
group by loan_number, communication_date
)
where <b>count1</b> > 1
);
rec cur%rowtype;
begin
open cur;
fetch cur into rec;
close cur;
end;
/
PL/SQL procedure successfully completed.
----------------------------------------------------------------------
|
|
|
Goto Forum:
Current Time: Sun Jun 30 23:13:37 CDT 2024
|