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

Question

I'd like to get in one query a post and the first comment associated with the post. Here is how I do it in PostgreSQL:

SELECT p.post_id, 
(select * from 
 (select comment_body from comments where post_id = p.post_id 
 order by created_date asc) where rownum=1
) the_first_comment
FROM posts p

and it works fine.

However, in Oracle I'm getting an error ORA-00904 p.post_id: invalid identifier.

It seems to work fine for one subselect, but I cannot get the comment with only one due to the fact that I need to use rownum (no limit / offset in Oracle).

What am I doing wrong here?

Probably, you use something `LIMIT 1` on `PostgreSQL`: it does not support `rownum`. — Quassnoi, Jan 12 '10 at 10:46

score 50 · Accepted Answer · answered Jan 12 '10 at 10:43

50

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

answered Jan 12 '10 at 10:43

Quassnoi

413,100
91
616
614

2

Thanks Quassnoi, that's of course a good answer (although stupidly complex, but probably PostgreSQL spoiled me a bit). Sorry for not replying earlier but my employer doesn't like me to use StackOverflow blocking the site and using proxy I can only ask questions, not comment (probably an issue with SO ajax code). – user248789 Jan 17 '10 at 02:52
2

If you get creative you can work around this problem by pulling the filtered variable up a level in the subquery. – chotchki Sep 30 '11 at 19:53
1

+1 for pointing Oracle's issue plus showing a workaround. PS: I do like Oracle, but sometimes Postgres is soooo much easier to use. – Christian Sep 25 '12 at 18:13
> No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does MySQL). This is a well-known problem. is this documented? – Vladimir Ulchenko Mar 21 '13 at 06:51
@vavan: yes, in the ANSI standard. – Quassnoi Mar 21 '13 at 08:09
I found the following in the oracle docs: "Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. The parent statement can be a SELECT, UPDATE, or DELETE statement in which the subquery is nested. A correlated subquery is evaluated once for each row processed by the parent statement. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement" yet I get ora-00904 when trying subquery in my view – Vladimir Ulchenko Mar 21 '13 at 08:13
@vavan: what Oracle version are you using? – Quassnoi Mar 21 '13 at 08:21
I can see that Tom also said "ANSI SQL has table references (correlation names) scoped to just one level deep" in http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932 – Vladimir Ulchenko Mar 21 '13 at 08:24
@vavan: this is not a correlated subquery (`IN (SELECT ...)`), this is a nested query (`SELECT FROM (SELECT ...)`). – Quassnoi Mar 21 '13 at 09:30
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26619/discussion-between-vavan-and-quassnoi) – Vladimir Ulchenko Mar 21 '13 at 10:10
This is also good when you need to use JOIN with subselect and need limit the number of rows selected for each parent row and cannot reference parent select. – PunchyRascal Aug 09 '18 at 15:30

score 3 · Answer 2 · answered Jan 12 '10 at 15:18

If you need SQL that is platform-independent, this will work:

SELECT p.post_id
     , c.comment_body
  FROM posts p
     , comments c
 WHERE p.post_id = c.post_id
   AND c.created_date IN
       ( SELECT MIN(c2.created_date)
           FROM comments c2
          WHERE c2.post_id = p.post_id
        );

But it assumes that (post_id, created_date) is the primary key of comments. If it isn't, you're going to get more than one line posts that have comments with the same created_date.

Also, it is likely to be slower than the solution that uses analytics, given by Quassnoi.

That would be a bit dangerous. Create date cannot be guaranteed to be unique. But just using MIN(comment_id) should be fine as it's taken from the sequence. I used Quassnoi's solution, though. — user248789, Jan 17 '10 at 02:55

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

2 Answers2

Linked