Oracle subquery in select

Question

I have a table that keeps costs of products. I'd like to get the average cost AND last buying invoice for each product.

My solution was creating a sub-select to get last buying invoice but unfortunately I'm getting

ORA-00904: "B"."CODPROD": invalid identifier

My query is

SELECT  (b.cod_aux) product,
    -- here goes code to get average cost,
    (SELECT round(valorultent, 2) 
     FROM (SELECT valorultent
           FROM pchistest
           WHERE codprod = b.codprod
           ORDER BY dtultent DESC) 
     WHERE ROWNUM = 1)
FROM pchistest a, pcembalagem b
WHERE a.codprod = b.codprod
GROUP BY a.codprod, b.cod_aux
ORDER BY b.cod_aux

In short what I'm doing on sub-select is ordering descendantly and getting the first row given the product b.codprod

Answer 1

Your problem is that you can't use your aliased columns deeper than one sub-query. According to the comments, this was changed in 12C, but I haven't had a chance to try it as the data warehouse that I use is still on 11g.

I would use something like this:

SELECT b.cod_aux AS product
      ,ROUND (r.valorultent, 2) AS valorultent
FROM pchistest a
JOIN pcembalagem b ON (a.codprod = b.codprod)
JOIN (SELECT valorultent
            ,codprod
            ,ROW_NUMBER() OVER (PARTITION BY codprod
                                    ORDER BY dtultent DESC)
            AS row_no
      FROM pchistest) r ON (r.row_no = 1 AND r.codprod = b.codprod)
GROUP BY a.codprod, b.cod_aux
ORDER BY b.cod_aux

I avoid sub-queries in SELECT statements. Most of the time, the optimizer wants to run a SELECT for each item in the cursor, OR it does some crazy nested loops. If you do it as a sub-query in the JOIN, Oracle will normally process the rows that you are joining; normally, it is more efficient. Finally, complete your per item functions (in this case, the ROUND) in the final product. This will prevent Oracle from doing it on ALL rows, not just the ones you use. It should do it correctly, but it can get confused on complex queries.

The ROW_NUMBER() OVER (PARTITION BY ..) is where the magic happens. This adds a row number to each group of CODPRODs. This allows you to pluck the top row from each CODPROD, so this allows you to get the newest/oldest/greatest/least/etc from your sub-query. It is also great for filtering duplicates.