多部分标识符无法绑定 [英] Multi-part identifier could not be bound

问题描述

我知道围绕SO出现此异常有几个问题，但没有发现对我有帮助的东西。

I know that there are several questions around this exception on SO, but nothing seen that helps me.

我有以下查询给我一个无法绑定多部分标识符'claim.fiData' -异常：

I have following query giving me a "Multi-part identifier 'claim.fiData' could not be bound"-Exception:

SELECT  claim.idData FROM tabData as claim 
 INNER JOIN dbo._previousClaimsByFiData(claim.fiData) AS prevClaim 
 ON prevClaim.idData=claim.fiData 
GROUP BY claim.idData
HAVING(prevClaim.fiMaxActionCode IN (8, 23, 24) and 
prevClaim.Repair_Completion_Date >= DATEADD(day,-90,prevClaim.Repair_Completion_Date))
ORDER BY claim.idData

previousClaimsByFiData 是一个表值函数，它返回所有以前的记录。您可以在此处找到它你很感兴趣
现在，我想使用maxActionCode IN（8，23，24）查找过去90天内具有先前声明的所有声明。

previousClaimsByFiData is a Table-Valued-Function that returns all previous records. You can find it here if you're interested. Now i want to find all claims that have previous claims in the last 90 days with a maxActionCode IN(8, 23, 24).

我也尝试过以下操作：

SELECT  count(*) FROM tabData as claim 
where exists(
 select 1 from dbo._previousClaimsByFiData(claim.fiData)as prevClaim where 
  prevClaim.fiMaxActionCode IN(8, 23, 24)and 
  prevClaim.Repair_Completion_Date >= DATEADD(day,-90,claim.Repair_Completion_Date)
)

但这给了我最大递归100已在语句完成前用完 -异常。

为什么我会得到这些例外以及如何避免它们？

Why do i get those exceptions and how to avoid them?

谢谢

编辑：
问另一个问题，该问题在主要问题上有所减少。

asked another question which is reduced on the main problem. I can delete this when i get an answer there.

更新：
Marc根据此此处。
所以要走的路是交叉应用。
但是，现在我有上面已经提到的下一个问题。几秒钟后，我收到语句完成之前已耗尽最大递归100的错误。
我不知道在何处添加OPTION（MAXRECURSION 0），因为如果我尝试将其添加到Inline-TVF中，则会得到语法错误。

UPDATE: Marc answered a simplified question according to this here. So the way to go is Cross Apply. But now i have the next problem what i've already mentioned above. I get a "The maximum recursion 100 has been exhausted before statement completion" Error after a few seconds. I dont know where to add the OPTION (MAXRECURSION 0) because i get a "Incorrect Syntax" if i try to add it in the Inline-TVF.

我当前的查询是：

SELECT  claim.idData FROM tabData  claim 
    CROSS APPLY dbo._previousClaimsByFiData(claim.fiData)AS tvfData 
GROUP BY claim.idData,claim.Repair_Completion_Date,tvfData.Repair_Completion_Date,tvfData.fiMaxActionCode
HAVING(tvfData.fiMaxActionCode IN (8, 23, 24) and 
tvfData.Repair_Completion_Date >= DATEADD(day,-90,claim.Repair_Completion_Date))
ORDER BY claim.idData

---

更新：解决方案是在SELECT语句的末尾添加OPTION（MAXRECURSION 0）。

推荐答案

解决方案是将OPTION（MAXRECURSION 0）添加到SELECT语句的末尾。
工作查询现在为：

The solution was to add the OPTION (MAXRECURSION 0) to the end of the SELECT Statement. The working query is now:

SELECT claim.idData,claim.Repair_Completion_Date,prevClaim.Repair_Completion_Date,prevClaim.fiMaxActionCode FROM tabData  claim 
    CROSS APPLY dbo._previousClaimsByFiData(claim.fiData)AS prevClaim 
GROUP BY claim.idData,claim.Repair_Completion_Date,prevClaim.Repair_Completion_Date,prevClaim.fiMaxActionCode
HAVING(prevClaim.fiMaxActionCode IN (8, 23, 24) and 
prevClaim.Repair_Completion_Date >= DATEADD(day,-90,claim.Repair_Completion_Date))
ORDER BY claim.idData
OPTION (MAXRECURSION 0)

这不是最快的查询（花费超过一分钟的时间才能在> 11Mio rec表中获得23000条记录），但是

It's not the fastest query(takes more than a minute to get 23000 records in a >11Mio rec table) but it works.

更新：以下查询要快得多（<4秒），并使用 EXISTS 而没有交叉申请：

UPDATE: The following query is much faster(<4 seconds) and uses EXISTS without CROSS APPLY:

  SELECT     idData
    FROM     tabData AS claim
    WHERE    fiProductType=1 and fiClaimStatus IN(1,5,7,8,9) AND EXISTS
         (SELECT idData
          FROM   dbo._previousClaimsByFiData(claim.fiData) AS prevClaim
          WHERE  (fiProductType = 1) AND (fimaxActionCode IN (8, 23, 24)) 
          AND (Repair_Completion_Date >= DATEADD(dd, - 90, DATEADD(dd, DATEDIFF(dd,0, claim.Repair_Completion_Date), 0))) 
          AND (Repair_Completion_Date <= claim.Repair_Completion_Date)) 
  order by claim.idData
  OPTION (MAXRECURSION 0)

这篇关于多部分标识符无法绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！