Getting error ORA-00904: "ID_NAME_COLL_TYPE": invalid identifier while creating view with-CodePudding

I am reading book Practical Oracle SQL. In this book in chapter 2 Author is creating a view with User defined types. Here are the queries

create or replace type id_name_type as object (
    id     integer, 
   name   varchar2(20 char)
);
/

create or replace type id_name_coll_type as table of id_name_type;
/

create or replace view customer_order_products_obj
as
select
    customer_id, 
    max(customer_name) as customer_name, 
    cast(
        collect(
            id_name_type(product_id, product_name)
            order by product_id
        ) as id_name_coll_type
    ) as product_coll
from customer_order_products
group by customer_id, id_name_coll_type;

But when I tried to run the view query then I got the error ORA-00904: "ID_NAME_COLL_TYPE": invalid identifier Then I narrow down the query and just run the following query. Just run select and remove the name from group by.

select
    customer_id, 
    max(customer_name) as customer_name, 
    cast(
        collect(
            id_name_type(product_id, product_name)
            order by product_id
        ) as id_name_coll_type
    ) as product_coll
from customer_order_products
group by customer_id;

The above query gives the following results

So what is wrong with the group by customer_id, id_name_coll_type;? Why in this case I am getting "ID_NAME_COLL_TYPE": invalid identifier. As it is a book example that's why I thought it should work as it is ? How view will create ?

Thanks

CodePudding user response：

id_name_coll_type is an identifier for a data type and not an identifier for a column; you must aggregate by columns.

As it is a book example that's why I thought it should work as it is?

Books can contain mistakes; you should check to see if there is an errata for the book that may contain a correction or if you have the latest version of the book. If not, you can e-mail the author/publisher and let them know of the mistake.

How view will create?

Do exactly what you did and do not include the id_name_coll_type identifier in the GROUP BY clause. You do not need to add the collection to the GROUP BY clause as COLLECT is an aggregation function.

db<>fiddle here