Oracle 11g order by and rownum in nested select does not work with inner join

Question

I want to return two columns where the second one is a username linked through a foreign id. I am worried there might exist such multiple records varying in the state, so I would like to order by a state (custom precedence) and get the first one found.

Here is the working query before the edit.

select entry.ID,
    (select username.USERNAME from USER_CREDENTIALS username
     inner join CREDENTIALS cred on username.ID = cred.ID
     where cred.SUBJECT_ID=entry.SUBJECTID
    )
from ENTRY entry
where entry.ID in (1, 2, 5);

My ultimate goal is to secure the inner select returns up to one record (no record results in null which is fine). So here I use rownum with ORDER BY a state *custom precedence) with respect to the issue with rownum and ORDER By in the same query described here (source sqlandplsql.com). I ended up with something like this:

select entry.ID,
       (select * from
           (select username.USERNAME from USER_CREDENTIALS username
            inner join CREDENTIALS cred on username.ID = cred.ID
            where cred.SUBJECT_ID=entry.SUBJECTID
            order by case when cred.STATE = 'A' then 1 else 2 end
           )
       where rownum=1
       )
from ENTRY entry
where entry.ID in (1, 2, 5);

This throws the exception:

[42000][904] ORA-00904: "ENTRY"."SUBJECTID": invalid identifier

I suspect only such attribute is visible only to the first-level nested select. However, I need to use the rownum-ORDER BY combo through another select which is not compliant with the "ENTRY"."SUBJECTID" visibility.

How to rewrite the queue to get rid of the error and make it rownum-ORDER BY safe?

Answer 1

Oracle does not (generally) allow correlation clauses in more than one level of nesting. Instead, you can use keep:

select e.ID,
       (select max(uc.USERNAME) keep (dense_rank first order by (case when c.STATE = 'A' then 1 else 2 end))
        from USER_CREDENTIALS uc join
             CREDENTIALS c 
             on uc.ID = c.ID
        where c.SUBJECTID = e.SUBJECTID
       )
from ENTRY e
where e.ID in (1, 2, 5);

keep is Oracle's fancy syntax for a "first" aggregation function.