1

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)
Thomas Dickey
  • 51,086
  • 7
  • 70
  • 105
jva
  • 2,797
  • 1
  • 26
  • 41

4 Answers4

1

What version are you using?

The exact same SQL works fine for me (Oracle Database 10g Express Edition Release 10.2.0.1.0).

Thilo
  • 257,207
  • 101
  • 511
  • 656
1

Oracle below 9i does not support ANSI join syntax.

Use this if you're on 8i and below:

SELECT  a.*
FROM    dual a,
        (
        SELECT  *
        FROM    dual
        WHERE   1 = 1
        ) b
WHERE   1 = 1
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • I've edited the question to show that my version (9.2) supports ANSI joins but not this one particular. – jva Oct 16 '09 at 09:40
1

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
DCookie
  • 42,630
  • 11
  • 83
  • 92
  • This is what solved my problem. Turns out it was a bug in SQL Navigator. Works fine in SQL*Plus. – jva Oct 19 '09 at 10:34
0

It looks correct to me and I am able to execute it in 10g, but it fails with 8i, which version of Oracle are you using?

pedromarce
  • 5,651
  • 2
  • 27
  • 27