0

I have a query in postgre, i convert it to oracle but it's can not run

update APITT_tbl_in set status ='Processing' where  
id = (select id from APITT_tbl_in where upper(status)='PENDING' 
and  ROWNUM <= 1 order by priority, id FOR update) 
returning reqid,command_name,api,request_msg,receive_node,command_type,id,sla,time_out,priority,receive_time

Below is error

TT1001: Syntax error in SQL statement  before or at: ""
Error at Line: 4 Column: 108

I know that this error is can not use FOR UPDATE in subquery, but i can not repair it :(

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Nguyen Anh Duc
  • 45
  • 1
  • 1
  • 12
  • You need to tell us whether you are doing it in **SQL** or **PL/SQL**? Also, apart from the syntax error, you have a functional mistake too by using **ROWNUM** and **ORDER BY** together. See [**How ROWNUM works in pagination query?**](http://stackoverflow.com/questions/30321483/how-rownum-works-in-pagination-query/30321788#30321788) – Lalit Kumar B Mar 09 '16 at 10:18
  • @LalitKumarB - the `TT1001` error and the tags indicate that this question is actually for the TimesTen database, which is an in-memory database marketed by Oracle; however, it's not the flagship Oracle RDBMS. I have no idea if TimesTen supports ROWNUM, or if it's supported in the same manner as is done in the flagship Oracle database. – Bob Jarvis - Слава Україні Mar 09 '16 at 12:10
  • @NguyenAnhDuc: if you know that you can't have `FOR UPDATE` in the subquery, why can't you just remove those words? – Bob Jarvis - Слава Україні Mar 09 '16 at 12:12
  • @BobJarvis You are correct, I didn't notice the error code. – Lalit Kumar B Mar 09 '16 at 12:52
  • I try to remove FOR UPDATE and use another fuction but can not get result – Nguyen Anh Duc Mar 10 '16 at 02:28

0 Answers0