SQL Inner join on select statements

Question

I am trying to make an inner join on a select statement like this:

select *
from (select* from bars  where rownum <= 10 )as tab1
inner join (select * from bars  where rownum <= 10 )as tab2
on tab1.close=tab2.close

and I get the following error: ORA-00933 SQL command not properly ended Any help would be appreciated, thank you!

Lenorelenox

Answer 1

Just remove as from your query:

select *
from (select* from bars  where rownum <= 10 ) tab1
inner join (select * from bars  where rownum <= 10 ) tab2
on tab1.close=tab2.close

Theoretics

Hi egorius, thanks, it worked. I still don't understand why sometimes oracle accepts the as and sometimes not – Lenorelenox

'As' can be (optionally) used before a COLUMN alias. TABLE aliases can't be prepended by 'as'. For example: "select count(*) as cnt from dual d". – Theoretics

You may want to have a look at SELECT syntax diagram (it's quite big, but defines exact syntax): download.oracle.com/docs/cd/B10501_01/server.920/a96540/… – Theoretics

I had the same problem that "as" fails when it joins empty table. Without "as" it works. – Agamete

Answer 2

I believe the error comes from you needing a semicolon to end the statement. The select looks fine to me otherwise.

Duran

Answer 3

select * from 
((select* from bars  where rownum <= 10 )as tab1
inner join (select * from bars  where rownum <= 10 )as tab2
on tab1.close=tab2.close)

Diabolic

Answer 4

just give a whitespace between ')' and 'as':

select * from (select* from bars  where rownum <= 10 ) as tab1
 inner join
 (select * from bars  where rownum <= 10 ) as tab2
 on
 tab1.close=tab2.close

Sigismund