help with Oracle query ( Missing right paranethesis)

I am getting "missing right parenthesis error while running this query "

select a.session_id,
       a.request_id,
       a.timestamp,
       a.queue_tag,
       b.*
  from (select session_id,
               request_id,
               timestamp,
               queue_tag,
               (select min(b.timestamp) nextrec
                  from tbl b
                 where a.session_id = b.session_id
                   and a.request_id = b.request_id
                   and b.timestamp > a.timestamp
                   and b.is_queue_empty = 0
               )
          from tbl a
         where is_queue_empty = 1 
            and nullif(queue_name,'') is null
       ) a
       left join tbl b 
    on a.session_id = b.session_id
   and a.request_id = b.request_id
   and a.nextrec = b.timestamp

Is it valid to select column开发者_Python百科 value like this in Oracle ? If not what I am missing here ?

I got the query above to work, had to move the column alias out (as suggested by @Martin) and removed redundant nullIf()

with tbl as(
            select SYSTIMESTAMP+1 timestamp, 0 is_queue_empty , '' queue_name , 'qt' queue_tag, -1 session_id , 1 request_id from dual
            union all
            select SYSTIMESTAMP timestamp, 1 is_queue_empty , '' queue_name , 'qt1' queue_tag, -1 session_id , 1 request_id from dual
            union all
            select SYSTIMESTAMP+1 timestamp, 0 is_queue_empty , '' queue_name , 'qt2' queue_tag, -2 session_id , 2 request_id from dual
            union all
            select SYSTIMESTAMP timestamp,  1 is_queue_empty , '' queue_name , 'qt22' queue_tag, -2 session_id , 2 request_id from dual            
)
select a.session_id,a.request_id,a.timestamp,a.queue_tag,
  b.*
from
(
    select session_id,request_id,timestamp,queue_tag,
     (select min(b.timestamp) nextrec
      from tbl b
      where a.session_id=b.session_id
        and a.request_id=b.request_id
        and b.timestamp > a.timestamp
        and b.is_queue_empty=0) nextrec --> had to put this outside the loop
    from tbl a
    where is_queue_empty=1 and queue_name is null --in oracle empty string is null thus nullif(queue_name,'') is redundant
) a
left join tbl b on a.session_id=b.session_id
               and a.request_id=b.request_id
               and a.nextrec = b.timestamp

You just need to add the alias nextrec after your scalar subquery. You name inside that query, but Oracle doesn't know the column name of it when it comes time to join it in the left join. This should work:

select a.session_id,
       a.request_id,
       a.timestamp,
       a.queue_tag,
       b.*
  from (select session_id,
               request_id,
               timestamp,
               queue_tag,
               (select min(b.timestamp) nextrec
                  from tbl b
                 where a.session_id = b.session_id
                   and a.request_id = b.request_id
                   and b.timestamp > a.timestamp
                   and b.is_queue_empty = 0
               ) nextrec
          from tbl a
         where is_queue_empty = 1 
            and nullif(queue_name,'') is null
       ) a
       left join tbl b 
    on a.session_id = b.session_id
   and a.request_id = b.request_id
   and a.nextrec = b.timestamp

Additionally when I run your original query on Oracle 10g I get the following more descriptive error from the database:

and a.nextrec = b.timestamp
           *
ERROR at line 44:
ORA-00904: "A"."NEXTREC": invalid identifier