Dinis Marques Dinis Marques - 1 month ago 6
SQL Question

How to SELECT FROM a identifier of a sub query

Why I can't SELECT FROM a identifier of a sub query?

SELECT e.num_enc "Codigo Encomenda", e.data_enc "Data Encomenda", a.quantidade "Quantidade"
FROM encomendas e, (SELECT e.num_enc num_enc,SUM(i.quantidade) quantidade
FROM itens i, encomendas e
WHERE i.num_enc = e.num_enc AND EXTRACT(YEAR FROM e.data_enc)=2014
GROUP BY e.num_enc) a,
(SELECT AVG(b.quantidade) media
FROM (SELECT SUM(i.quantidade) quantidade
FROM a) c #PROBLEM HERE
WHERE e.num_enc = a.num_enc AND a.quantidade>c.media;

Answer

NO, you are trying to select from inline view / subquery immediately which can't be done. You can access that subquery in outer query (OR) you can JOIN with it and get the data.

For your purpose, you can consider using a CTE (Unless you are using MySQL).

You can re-write your query to be like

SELECT e.num_enc "Codigo Encomenda", 
e.data_enc "Data Encomenda", 
a.quantidade "Quantidade"
FROM encomendas e
JOIN (SELECT e.num_enc num_enc,SUM(i.quantidade) quantidade
                FROM itens i, encomendas e
                WHERE i.num_enc = e.num_enc AND EXTRACT(YEAR FROM e.data_enc)=2014
                GROUP BY e.num_enc) a ON e.num_enc = a.num_enc;

This below part (where error comes) looks illogical to me since you are trying to get AVG() from SUM() result

 (SELECT AVG(b.quantidade) media
            FROM (SELECT SUM(i.quantidade) quantidade
                  FROM a) c #PROBLEM HERE
Comments