Querying Oracle PLSQL

Question

This is the error message I get when I run the below scripts

    ERROR at line 1:
    ORA-00979: not a GROUP BY expression
    ORA-06321: at "s3398293.P2", line 7
    ORA-06321: at "s3398293.P2", line 18
    ORA-06321: at line 1


    create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
        staff_info  staff.bno%TYPE;
        address_info varchar2(20);

            CURSOR c1 IS
                SELECT staff.bno ,
                branch.street || ' ' || branch.suburb || ' ' || branch.postcode 
                FROM deal , staff, contact , property , branch
                where staff.peid = contact.peid
                and contact.pno = property.pno
                and property.pno = deal.pno 
                and staff.peid = branch.peid
                group by staff.bno
                HAVING x > sum(deal.price);

    BEGIN
         OPEN c1;
         LOOP
            FETCH c1 INTO staff_info,address_info ;
            EXIT WHEN c1%notfound;
            dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
            dbms_output.put_line(staff_info ||' '|| address_info);  
         END LOOP;
         close c1;
    END;
    /

I am trying to get The Staff.bno and address of the branch where x > sum(deal.price)

    Can Someone tell me more about GROUP BY EXPRESSION! ?

What are you trying to do? Are you wanting to return those values or simply work with them. Your code example seems a bit odd as you just lplacing your data into a cursor but not doing anything with it. — Alistair Laing
– Alistair Laing, Commented Oct 12, 2011 at 5:49
This question would have beentrivial for you to have solved yourself by simply reading the documentation: download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/… — APC
– APC, Commented Oct 12, 2011 at 14:18

Sodved · Accepted Answer · 2011-10-13 05:03:22Z

2

You need to move x > sum(deal.price) into a HAVING clause after the group by. What you have written is invalid (ignoring the double WHERE) because where clause is evaluated before the SUM/GROUP BY. e.g.

SELECT staff.bno ,
        branch.street||' '||branch.suburb||' '||branch.postcode 
FROM deal , staff, contact , property 
where staff.peid = contact.peid
and contact.pno = property.pno
and property.pno = deal.pno 
group by staff.bno, branch.street||' '||branch.suburb||' '||branch.postcode
HAVING x > sum(deal.price);

Edit: forgot to group by the other expression you are selecting.

edited Oct 13, 2011 at 5:03

answered Oct 12, 2011 at 6:22

Sodved

8,6272 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

APC Over a year ago

@shakur007 - precisely what errors are you getting? If you want us to solve your problem you need to give us more information to go on.

APC · Accepted Answer · 2011-10-12 14:06:43Z

One obvious problem with your code is that CURSOR. Cursors are variables and so need to be defined in the DECLARATION section. Then you need to open the curosr in the package body. Also you need to check whether the FETCH actually retrieves a row.

create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
       CURSOR c1 for
            SELECT staff.bno ,
                branch.street||' '||branch.suburb||' '||branch.postcode
                FROM deal , staff, contact , property 
                where staff.peid = contact.peid
                and contact.pno = property.pno
                and property.pno = deal.pno 
                group by staff.bno
                HAVING x > sum(deal.price);
      staff_info  staff.bno%TYPE;
      address_info address%TYPE;
    BEGIN
         open c1;
         loop
            fetch c1 into staff_info,address_info ;
            exit when c1%notfound;
            dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
            dbms_output.put_line(staff_info ||' '|| address_info);  
         end loop;
         close c1;
    END;

Cursors like this are not usually required. It is way more efficient to use an implicit cursor.

create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
    BEGIN
         for r in ( SELECT staff.bno ,
                           branch.street||' '||branch.suburb||' '||branch.postcode as address_info
                    FROM deal , staff, contact , property 
                    where staff.peid = contact.peid
                    and contact.pno = property.pno
                    and property.pno = deal.pno 
                    group by staff.bno
                    HAVING x > sum(deal.price))
         loop
            dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
            dbms_output.put_line(r.bno ||' '|| r.address_info);  
         end loop;
    END;

Collectives™ on Stack Overflow

Querying Oracle PLSQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related