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

ora-00904, oracle, sql

Solution

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

Problem

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?

Original source