Search code examples
oracle-databasejoinansi-sqlora-00900

WHERE conditions in subquery while using ANSI joins


Why doesn't it work?

SELECT a.*
FROM dual a
     JOIN (SELECT * FROM dual WHERE 1=1) b
     ON (1=1);

I get "ORA-00900: invalid SQL statement". Is there a way to use WHERE clause inside the subquery?

Edit: Version 9.2

SELECT *
FROM v$version

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

The following executes just fine:

SELECT a.*
FROM dual a
     JOIN (SELECT * FROM dual /*WHERE 1=1*/) b
     ON (1=1)

Solution

  • It works for me on 9.2 (32 bit version is the only difference):

    SQL> SELECT a.*
      2  FROM dual a
      3       JOIN (SELECT * FROM dual WHERE 1=1) b
      4       ON (1=1);
    
    D
    -
    X
    
    SQL> quit
    Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    With the OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 - Production