我有一个在当前MySQL数据库上运行的查询。现在我被迫转移到Oracle,因此我正在尝试将所有存储过程/程序移植到使用Oracle SQL语法。我在一个特定的查询上遇到了很多麻烦。以下是MySQL查询。它使用子查询更新表。
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
在Oracle中,我无法使用limit命令,因此我寻找了解决方法。这是我的Oracle查询。(但它不起作用。)
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
我遇到了以下错误:
Error at Command Line:8 Column:29
Error report:
SQL Error: ORA-00904: "ALF"."LASTTRADEDATE": invalid identifier
00904. 00000 - "%s: invalid identifier"
第8行是:
AND alf2.lasttradedate > alf.lasttradedate
移除UPDATE语句并在子查询中插入一些虚拟值可使子查询的结果正确:
(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)
看到这个错误,似乎是对alf的第二个引用没有起作用。有什么想法可以改变查询语句,使其在oracle中起作用吗?
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