How do I access a LISTAGG that is used in sub query?-CodePudding

I have the query below, I am trying to select inner_query.ship_codes.

If I do inner_query.item_id_alias it works fine, but how can I select the ship_codes from my inner_query ?

SELECT inner_query.item_id_alias
FROM (
  SELECT li.item_id as item_id_alias,
         LISTAGG(a.ship_code, '; ')
           WITHIN GROUP (ORDER BY a.ship_code) as "ship_codes"
  FROM (
         SELECT DISTINCT
                ship_code, 
                MAX(order_id) as order_id
         FROM   orders o
         WHERE  o.order_id in (
                  SELECT li2.order_id
                  FROM   line_items li2 
                  GROUP BY li2.order_id
                ) 
         GROUP BY ship_code
       )a
       INNER JOIN line_items li ON a.order_id = li.order_id      
  GROUP BY li.item_id
) inner_query;

CodePudding user response：

Use the column alias. Since you have used a quoted identifier in the sub-query then you need to use the quoted identifier in the outer query (and anywhere else you may want to reference it):

SELECT inner_query.item_id_alias,
       inner_query."ship_codes"
FROM (
  SELECT li.item_id as item_id_alias,
         LISTAGG(a.ship_code, '; ')
           WITHIN GROUP (ORDER BY a.ship_code) as "ship_codes"
  FROM (
         SELECT DISTINCT
                ship_code, 
                MAX(order_id) as order_id
         FROM   orders o
         WHERE  o.order_id in (
                  SELECT li2.order_id
                  FROM   line_items li2 
                  GROUP BY li2.order_id
                ) 
         GROUP BY ship_code
       )a
       INNER JOIN line_items li ON a.order_id = li.order_id      
  GROUP BY li.item_id
) inner_query;

Alternatively, you can use a wildcard:

SELECT *
FROM   (...) inner_query

or a wildcard with the alias for the sub-query:

SELECT inner_query.*
FROM   (...) inner_query

or the identifiers without the alias for the sub-query:

SELECT item_id_alias,
       "ship_codes"
FROM   (...) inner_query

(Note: using DISTINCT and GROUP BY in the same SELECT statement is pointless. You can remove the DISTINCT and the output will not change.)