Correlated subquery with join in oracle 11.2 select clause doesn't works

Question

The following correlated subquery works very well in 12c but fails with mentioned error in 11.2. Can anyone help with this? Thanks in advance.

Using 11.2.0.4

SQL> column member1 format A35
column member2 format A35
select a.group#, a.SEQUENCE#, a.ARCHIVED, a.STATUS,
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 1) as Member1,
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 2) as Member2,
(a.BYTES/1024/1024)
from v$log a;SQL> SQL>   2    3    4    5  
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 1) as Member1,
                                                                                 *
ERROR at line 2:
ORA-00904: "A"."GROUP#": invalid identifier

Using 12c

GROUP# SEQUENCE#  ARC STATUS   MEMBER1                        MEMBER2                        (A.BYTES/1024/1024)
------ ---------- --- -------- ------------------------------ ------------------------------ -------------------
 7     4901       YES INACTIVE /u02/oradata/PROD/redo06.log   /u02/oradata/PROD/redo07.log                   810
 9     4902       YES INACTIVE /u02/oradata/PROD/redo9.log    /u03/oradata/PROD/redo9.log                    808
10     4903       NO  CURRENT  /u02/oradata/PROD/redo10.log   /u03/oradata/PROD/redo10.log                   808

Answer 1

Someone please correct me if I am wrong, but without ORDER BY clauses in you sub-selects, you are not guaranteed to get different members.

I believe this SQL will get you the desired results. I have tested it on Oracle 11.

WITH
    aset
    AS
        (SELECT MEMBER
              , group#
              , ROW_NUMBER ()
                    OVER (
                        PARTITION BY group# ORDER BY MEMBER
                    )
                    rn
           FROM v$logfile)
SELECT a.group#
     , a.sequence#
     , a.archived
     , a.status
     , member1
     , member2
  FROM v$log a
       LEFT OUTER JOIN (SELECT MEMBER AS member1, group#
                          FROM aset
                         WHERE rn = 1) aset1
           ON a.group# = aset1.group#
       LEFT OUTER JOIN (SELECT MEMBER AS member2, group#
                          FROM aset
                         WHERE rn = 2) aset2
           ON a.group# = aset2.group#;