So I've got a nested query that works fine by itself but results in an error message when used in a PL/SQL block: (ORA-00979: not a GROUP BY expression) when running it in SQL developer.
When I format the code in SQL developer (such that ‘substr’ is on a different line from its parameters), or run the same code on Oracle Live SQL, it works fine.
Note: I know that this nested query can be simplified and broken down into several queries. I'm just curious (1) why the nested query runs fine by itself but causes an error in a block (2) the error only exists in SQL developer in the current format, but disappears when formatted, or run on oracle live SQL.
The PL/SQL block that causes an error in SQL developer:
declare
V_CUSTOMER_NUMBER INTEGER;
begin
select max(numbers_of_customer) into V_CUSTOMER_NUMBER
from (select substr(phone, 0, 3) as areacode, count(*) as numbers_of_customer from customer group by substr(phone, 0, 3));
DBMS_OUTPUT.PUT_LINE(V_CUSTOMER_NUMBER);
end;
The error message:
Error starting at line : 1 in command - declare V_CUSTOMER_NUMBER INTEGER; begin select max(numbers_of_customer) into V_CUSTOMER_NUMBER from (select substr(phone, 0, 3) as areacode, count(*) as numbers_of_customer from customer group by substr(phone, 0, 3)); DBMS_OUTPUT.PUT_LINE(V_CUSTOMER_NUMBER); end; Error report - ORA-00979: not a GROUP BY expression ORA-06512: at line 4 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action:
And the error disappears when formatted:
DECLARE
v_customer_number INTEGER;
BEGIN
SELECT
MAX(numbers_of_customer)
INTO v_customer_number
FROM
(
SELECT
substr(
phone, 0, 3
) AS areacode,
COUNT(*) AS numbers_of_customer
FROM
customer
GROUP BY
substr(
phone, 0, 3
)
);
dbms_output.put_line(v_customer_number);
END;
And the nested query works fine on its own.
select max(numbers_of_customer)
from (select substr(phone, 0, 3) as areacode, count(*) as numbers_of_customer from customer group by substr(phone, 0, 3));
Thank you!
Edit: below are the table definition, in case you want to try on your end.
create table Customer(
CustomerID varchar2(10) ,
CompanyName varchar2(40) ,
CustFirstName varchar2(15) ,
CustLastName varchar2(20) ,
CustTitle varchar2(5) ,
Address varchar2(40) ,
City varchar2(20) ,
State varchar2(2) ,
PostalCode varchar2(10) ,
Phone varchar2(12) ,
Fax varchar2(12) ,
EmailAddr varchar2(50)
);