紧急求救一个db2存储过程转为oracle的

这个问题很烦人，弄我老长时间了。我db2不太好，请高手帮忙。

以下是其中的一段：可是换为oracle的语句，就会报ora-00937错误

 set account_temp2 = (
                 coalesce(
                        (
                        select coalesce(sum(bargainQuantity),0)-
                        coalesce(
                            (
                               select sum(bargainQuantity) bargainQuantity_1
                               from secTradeFlow,secTradeType,secCapitalAccount
                               where secTradeFlow.SECCAPACCOUNTID = secCapitalAccount.ID
                               and secCapitalAccount.STATUS = 2
                               and secTradeFlow.bizprojectid = @ProjectID
                               and thisdate < DEAL_DATE
                               and dealType = 2
                               and secTradeFlow.secInfoId = DEAL_INFOID
                               and secTradeFlow.secTradeTypeId = secTradeType.id
                               and secTradeFlow.syscurrencyid = @CurrencyID
                               and confirmFlag <> 1 and year(thisdate)=  year(DEAL_DATE)
                            ),0
                        )+coalesce(
                            (
                                select sum(bargainQuantity) bargainQuantity_2
                                from secTradeFlow,secTradeType,secCapitalAccount
                                where secTradeFlow.SECCAPACCOUNTID = secCapitalAccount.ID
                                and secCapitalAccount.STATUS = 2
                                and secTradeFlow.bizprojectid = @ProjectID
                                and thisdate <= DEAL_DATE
                                and dealType = 6
                                and secTradeFlow.secInfoId = DEAL_INFOID
                                and secTradeFlow.secTradeTypeId = secTradeType.id
                                and secTradeFlow.syscurrencyid = @CurrencyID
                                and confirmFlag <> 1 and year(thisdate)=  year(DEAL_DATE)
                            ),0
                        ）
急请高手帮忙，万份感谢！！！！

补充：http://topic.zhaoxi.net/u/20090314/23/e3062b86-4553-4144-b726-e80889362af7.html?seed=1777628598
请大家在这里帮看看

答案：你这段sQL就没有写完整，让我怎么看啊。
因为我这里没有你的环境，也没有办法测试，不过sql 很复杂的时候，可能会出现一些预料不到的编译错误，像你写得这个，看着感觉没错，可是却给你报错。
你可以尝试用cursor在存储你的结果，然后再赋给你的变量试试。

上一个：Quest Central for DB2 v4.9 用quest修改保存是总是无响应
下一个：SOA Websphere DB2 三者是什么关系？