CODEWITHSUNDEEP
oracle-database
user1205746
user1205746

Reputation: 3366

can LISTAGG be used in view?

I have a sql statement that works properly and yields proper result.

SELECT "StoreId",  LISTAGG("ProductCategory"
                          || ':'
                          || '('
                          || ProductIds
                          || ')', ', ') WITHIN GROUP (
ORDER BY "ProductCategory") AS ProductsAndCategories
FROM
  (SELECT "StoreId",
    "ProductCategory",
    LISTAGG("ProductId", ',') WITHIN GROUP (
  ORDER BY "ProductId") AS ProductIds
  FROM SUPERMARKET
  GROUP BY "StoreId",
    "ProductCategory"
  ) s
GROUP BY "StoreId";

However, if I use the exact same statement and put it in View, I got a compiler error that does not quite make sense (see below). Someone told me that it works as there is no limitation on using LISTAGG with view, but it clearly errors out on me. I am not sure what is wrong with my statement. Could someone point it out to me?

enter image description here

Upvotes: 0

Views: 1654

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Your problem appears to be with the GUI.

You can create a view using:

create view v_storeproducts as
    SELECT "StoreId", 
            LISTAGG("ProductCategory"
                              || ':'
                              || '('
                              || ProductIds
                              || '
                   )', ', ') WITHIN GROUP (ORDER BY "ProductCategory") AS ProductsAndCategories
    FROM (SELECT "StoreId",
                 "ProductCategory",
                  LISTAGG("ProductId", ',') WITHIN GROUP (ORDER BY "ProductId") AS ProductIds
          FROM SUPERMARKET
          GROUP BY "StoreId",
          "ProductCategory"
         ) s
    GROUP BY "StoreId";

Upvotes: 2

Related Questions