Oracle Subquery Does Not See the Variable from the Outer Block 2 Levels Up

Oracle subquery does not see the variable from the outer block 2 levels up

No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does MySQL).

This is a well-known problem.

Use this:

SELECT  p.post_id, c.*
FROM    posts
JOIN    (
        SELECT  c.*, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_date ASC) AS rn
        FROM    comments c
        ) c
ON      c.post_id = p.post_id
        AND rn = 1

Oracle subquery in select

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.

Subquery that accesses main table fields combined with LIMIT clause in Oracle SQL

Please try with analytic function:

with tp as (select t.*, row_number() over (partition by taskListCode order by importance desc) r 
            from tasks t 
            where isReady = 1 /*or 'Y' or what is positive value here*/)
select u.name, tp.description 
  from users u left outer join tp on (u.taskListCode = tp.taskListCode) 
  where tp.r = 1;

Subquery function invoked twice if alias is used in main SQL

The SQL engine is opting to not materialize the subquery and is pushing the function calls into the outer query where it gets called multiple times for each row. You need to force the function to be evaluated in the subquery where it is called rather than allowing the SQL engine to rewrite the query.

One method is use ROWNUM to force it to materialize the inner query:

SELECT A1 + A1
  FROM (SELECT FCN_SLOW () AS A1
          FROM DUAL
         WHERE ROWNUM >= 1)

Another method is to use a CTE with the (undocumented) materialize hint:

WITH slow_query(a1) AS (
  SELECT /*+ materialize */
         FCN_SLOW ()
  FROM   DUAL
)
SELECT A1 + A1
  FROM slow_query

db<>fiddle here [which takes 20 seconds to run... not 30 seconds.]

You can see a similar example with materializing sequence values (rather than sleeping) in this answer.

Fetch subquery value from cursor to parameter in PL/SQL

The columns of sub queries are not projected so you can't reference them. If you want the CUSTOMER_ID and NEW_REFERENCE_ID in your program you will have to select them in the top level SELECT clause.
The easiest answer to your solution is to just open a cursor for your
subquery itself, for eample:

BEGIN
  FOR cur IN (SELECT crs_cust.CUSTOMER_ID, subset.NEW_REFERENCE_ID
               FROM CRS_CUSTOMERS crs_cust
                INNER JOIN DAY0_SUBSET subset ON 
                  crs_cust.CUSTOMER_ID=subset.CURRENT_CUSTOMER_ID)
  LOOP
     DBMS_OUTPUT.PUT_LINE(cur.customer_id || ', ' || cur.new_reference_id);
  END LOOP;
END;
/

PLSQL - IN clause using variable with multiple values

Declare the type globally:

CREATE TYPE number_list IS TABLE OF NUMBER;

Then you can initialise the list and then, in each iteration of the loop, EXTEND the list and assign the value and, finally, use the MEMBER OF operator or IN with a subquery and table collection expression:

DECLARE
  l_id_list NUMBER_LIST := NUMBER_LIST();
BEGIN
  FOR i IN 1..l_row_count
  LOOP
    l_id_list.EXTEND;
    l_id_list(l_id_list.COUNT) := to_number(
                                    apex_json.get_varchar2(
                                      p_path   => 'rows[%d].id', 
                                      p0       => i,
                                      p_values => l_values
                                    )
                                  );
  END LOOP;

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid MEMBER OF l_id_list;

  -- or

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid IN (SELECT COLUMN_VALUE FROM TABLE(l_id_list));
END;

---

Related Topics