CODEWITHSUNDEEP
sqloracle-databasesyntax-errorsubquery
user_vs
user_vs

Reputation: 1033

Inner Query For Oracle is not working?

I have 2 tables table1 and table2. I want fetch data from two tables. the query is

SELECT tb2.tdn
     ,tb2.nrn
     ,tb2.id
     ,tb2.dat
     ,tb2.mcheck
     ,tb2.info
     ,tb2.edrf
     , (SELECT count(*) 
        from table1 tb1
        where tb1.id = 'ftam' 
        and tb1.tdn =  tb2.tdn 
        and (tb1.display = 'Y' OR tb1.display = 'y') ) as history 
from (select rownum rnum 
             ,table2.* 
      FROM  (SELECT * 
             FROM table2 
             WHERE id = 'ftam' 
             and (display = 'Y' OR display = 'y') 
             ORDER BY dat DESC ) table2 tb2 
     where  rownum <= 50 )
WHERE rnum >  0

showing 

SQL Error [907] [42000]: ORA-00907: missing right parenthesis  
ORA-00907: missing right parenthesis

What is wrong with query? I have implemented it in MySQL, but when I changed query to Oracle it is showing the error.

Thanks

Upvotes: 0

Views: 68

Answers (2)

APC
APC

Reputation: 146239

Altering the posting layout for clarity reveals that this line is the immediate problem:

ORDER BY dat DESC ) table2 tb2

You need one alias, not two. So it should be

ORDER BY dat DESC )  table2

You also need to put an alias on the outer nested query:

(select rownum rnum 
             ,table2.* 
      FROM  (SELECT * 
             FROM table2 
             WHERE id = 'ftam' 
             and (display = 'Y' OR display = 'y') 
             ORDER BY dat DESC ) table2
     where  rownum <= 50 ) tb2

Upvotes: 2

Moudiz
Moudiz

Reputation: 7377

Your query needs a lot of improvement , if you tell us what are your trying to do, we can maybe help you with building the query.

In your query id is a character , its better to be number . you add condition ='y' or ='Y' , you can added lower/upper fuction.

this a suggestion to fix your query .

   select rownum rnum, table2.*
     FROM table2
   WHERE id = 'ftam'
   and lower(display) = 'Y' and rownum <= 50
   ORDER BY dat DESC

Upvotes: -1

Related Questions