error in SQL developer when using subquery in PLSQL

Viewed 47

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)    
);
0 Answers
Related