CODEWITHSUNDEEP
sqloracle-databaseplsql
Rowan Frank
Rowan Frank

Reputation: 117

Getting a "Not a group function error" PLSQL

I have to create a block that receives a region via prompt and returns its number of countries. If the region id is negative and no data is found, have to raise exceptions

DECLARE
region Regions.Region_Name%type := '&Insert_the_name_of_the_region';
id_region Regions.Region_Id%type;
negative_exception exception;
countries_nr number;
BEGIN
select r.region_id into id_region, count(c.country_id) into countries_nr from countries c inner join regions r on c.region_id=r.region_id where r.region_name = region group by r.region_id;

If regiao_id < 0 Then
      raise negativo_exception;
     Else
     DBMS_OUTPUT.PUT_LINE(countries_nr);
    End If;

EXCEPTION
WHEN No_data_found then
 dbms_output.put_line('No country found on region'||region);
 WHEN negative_exception THEN
 dbms_output.put_line('Region with negative ID');
END;
/

Im getting the error

ORA-06550: line 8, column 36:
PL/SQL: ORA-00934: group function is not allowed here
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Upvotes: 0

Views: 51

Answers (1)

Popeye
Popeye

Reputation: 35910

You are using INTO incorrectly.

Use it as follows:

select r.region_id , count(c.country_id) 
  into id_region   , countries_nr
.....

Upvotes: 1

Related Questions