4

I have a MySQL query that works on my current MySQL database. I've been forced to move over to oracle, so I'm trying to port all my stored procedures / programs to use the Oracle SQL Syntax. I'm having a lot of trouble on one particular query. Here is the MySQL query. It updates a table using a subquery.

 update table1 alf 
 set nextcontractid = 
 (
 select 
    contractid from table1copy alf2 
 where 
    alf2.assetid = alf.AssetID 
 and 
    alf2.lasttradedate > alf.LastTradeDate 
 order by lasttradedate asc limit 1
 ) 
 where complete = 0

In oracle, I can't use the the limit command, so I've looked for the workaround. Here is my oracle query. (which doesn't work.)

 update table1 alf
 set nextcontractid = 
   (select contractid from
     (
     SELECT contractid, rownum as row_number
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     ORDER BY lasttradedate ASC
   ) 
   where row_number = 1)
 where alf.complete = 0

I get the following error:

 Error at Command Line:8 Column:29
 Error report:
 SQL Error: ORA-00904: "ALF"."LASTTRADEDATE": invalid identifier
 00904. 00000 -  "%s: invalid identifier"

line 8 is:

 AND alf2.lasttradedate > alf.lasttradedate

Removing the update statement and putting in some dummy values into the subquery yields the correct results for the subquery:

   (select contractid from 
     ( 
     SELECT contractid, rownum as row_number 
     FROM asset_list_futures_copy alf2 
     WHERE alf2.assetid     = 'GOLD' 
     AND alf2.lasttradedate > '20110101' 
     ORDER BY lasttradedate ASC 
     )  
    where row_number = 1)

Looking at the error, it looks like the second reference to alf isn't working. Any idea how I can change my query so that it works in oracle?

Brian
  • 651
  • 1
  • 7
  • 13
  • Not 100% sure, I'm thinking: `SELECT contractid, rownum as row_number FROM asset_list_futures_copy alf2 WHERE alf2.assetid = 'GOLD' AND alf2.lasttradedate > '20110101' HAVING ROWNUM = 1 ORDER BY lasttradedate ASC` – Johan Sep 01 '11 at 13:32
  • 1
    I'm not grouping by anything. The "Having" keyword is used when you group by something correct? (http://psoug.org/reference/group_by.html) I think the problem is that I have 2 nested queries within the update (the outer one just selects the top entry off the inner one which selects a bunch of data and orders it) and the inner query doesn't know what alf is. – Brian Sep 01 '11 at 13:51

2 Answers2

2

Seems the parser does not like that, despite the fact it is sintacticaly correct. Probably the two imbricated and ordered clause is blinding him somehow. I reproduced that.

You can use an analytical function:

update table1 alf
 set nextcontractid = 
     (SELECT min(contractid) keep (dense_rank first order by lasttradedate asc) 
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     )
 where alf.complete = 0
Florin Ghita
  • 16,995
  • 5
  • 52
  • 72
0

You can use WHERE rownum = 1, or rownum BETWEEN x AND y in cases that you want more results.

Narf
  • 14,118
  • 3
  • 35
  • 63
  • 1
    That works, if I don't use two nested queries. However, that only works if the order by in Ascending. (I guess my example was bad). If you want descending order, you'll have to get the max(rownum), which requires two nested queries, and the same problem pops up again. – Brian Sep 01 '11 at 14:13
  • Indeed, Oracle sucks at this. But this is the only alternative to `LIMIT` that there is (as far as I know) and additional nesting is pretty common. – Narf Sep 02 '11 at 07:16