SQL Anywhere错误-824：非法参考相关名的表名

Question

当我运行的Sybase IQ此脚本：

declare @YEAR int=2017 
declare @MON int=6 
declare @DAY int=7 

update MainTable 

set MainTable.Amount=(X.Number+Y.Number), 
    MainTable.Total=(X.Total+Y.Total) 

from (select 'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end 
     from Table1  
     where T1_Account_NO=MainTable.Account_NO 
     and [email protected] and [email protected] and [email protected]) X, 
    (select 'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
     from Table2 where T2_Account_NO = MainTable.Account_NO 
     and [email protected] and [email protected] and [email protected])Y 

where  [email protected] 
     and MainTable.MON = @MON 
     and [email protected] 

我得到了这样的错误：“SQL Anywhere错误-824：非法参考相关名MainTable“

我该如何超越这个问题？

Answer 1

您是否尝试过加入MainTable到from条款，如：

update Maintable 
set ... 
from MainTable, 
     (select ...)X, 
     (select ...)Y 
where ... 

---

注：我与Sybase ASE，即不允许 '外部' 相关名工作在派生表中引用，所以我想知道SQLAnywhere是否有类似的限制...？

如果你拉MainTable加入了到最顶层的查询会发生什么，如：

declare @YEAR int=2017 
declare @MON int=6 
declare @DAY int=7 

update MainTable 

set MainTable.Amount=(X.Number+Y.Number), 
    MainTable.Total=(X.Total+Y.Total) 

from (select T1_account_NO, 'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end 
     from Table1  
     where [email protected] and [email protected] and [email protected] 
     group by T1_Account_NO) X, 
    (select T2_Account_NO, 'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
     from Table2 where [email protected] and [email protected] and [email protected] 
     group by T2_Account_NO)Y 

where  [email protected] 
     and MainTable.MON = @MON 
     and [email protected] 
     and MainTable.Account_NO = X.T1_Account_NO 
     and MainTable.Account_NO = Y.T2_Account_NO 

一个潜在的与性能相关的缺点是，如果派生表现产生大集不会与MainTable连接的记录（除非SQLAnywhere查询引擎能够以某种方式扁平查询... ???）。

---

如果这不是允许派生表“外部”相关的名字，另一个（明显？）的问题的解决办法是从表1 /表2加入MainTable的结果创建一对夫妇#TEMP表，然后将MainTable更新为#temp表的连接。 [可能索引#temp表，如果数据量足够大，性能明智，索引。]

Answer 2

您是否尝试将MainTable添加到FROM子句中？

Answer 3

我超越这个问题是这样的：

declare @YEAR int=2017 
declare @MON int=6 
declare @DAY int=7 

update MainTable 
set MainTable.Amount= (X.Number), 
    MainTable.Total = (X.Total) 
from (select T1_Account_NO,'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end 
     from Table1  
     where [email protected] and [email protected] and [email protected] 
     group by T1_Account_NO) X, 
where X.T1_Account_NO=MainTable.Account_NO 
     and [email protected] 
     and MainTable.MON = @MON 
     and [email protected] 


update MainTable 
set MainTable.Amount= coalesce(MainTable.Amount,0)+(Y.Number), 
    MainTable.Total = coalesce(MainTable.Total,0)+(Y.Total) 
    (select T2_Account_NO,'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
     from Table2 
     where [email protected] and [email protected] and [email protected] 
     group by T2_Account_NO) Y 

where  [email protected] 
     and MainTable.MON = @MON 
     and [email protected] 
     and Y.T2_Account_NO = MainTable.Account_NO 

我已经分隔更新脚本中的两个部分。