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 Go to next message
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 Go to previous message
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.


----------------------------------------------------------------------
Previous Topic: sql-pl/sql help needed urgent
Next Topic: Re: Pass Table Name as a Variable
Goto Forum:
  


Current Time: Fri Mar 29 09:52:06 CDT 2024