时间戳

首页 > 解决方案 > 带有 SQL 表达式/子查询的 Crystal Reports 中的“无法绑定多部分标识符”

sql - 带有 SQL 表达式/子查询的 Crystal Reports 中的“无法绑定多部分标识符”

问题描述

我正在使用 Crystal Reports，它使您能够创建称为 SQL 表达式的东西。它们基本上是子查询，但受到限制，因此每个 SQL 表达式只能返回一个标量值。欲了解更多信息，请访问：http: //www.cogniza.com/wordpress/2005/11/07/crystal-reports-using-sql-expression-fields/

引用的文章非常明确地说：

If you are using a SQL Expression as a subquery and wish to link it to the 
detail row of your main report, do not include the table you wish to link to 
in the FROM clause of the subquery. For example:

(
SELECT  MAX("FLOWSHEET"."VALUE")
FROM    FLOWSHEET
WHERE   "FLOWSHEET"."ID" in ('11')
AND "FLOWSHEET"."IP_ID" = "HOSPITAL_VISIT"."IP_ID"
)

The above query links to the HOSPITAL_VISIT table in the main report by 
linking to the IP_ID field. To accomplish this, HOSPITAL_VISIT is omitted 
from the FROM clause in the query.

我的报告中有一个主要查询：

SELECT
HIFIS_HousePlacements.MovedInDate, 
vw_ClientBasics.LastName, 
vw_ClientBasics.FirstName,
HIFIS_Clients_HousePlacements.ClientID
FROM   
HIFIS_Clients_HousePlacements 
INNER JOIN HIFIS_HousePlacements ON HIFIS_Clients_HousePlacements.HousePlacementID=HIFIS_HousePlacements.HousePlacementID
INNER JOIN vw_ClientBasics ON HIFIS_Clients_HousePlacements.ClientID=vw_ClientBasics.ClientID

现在我想添加一个 SQL 表达式

(
SELECT TOP 1 AssessmentAcuityType
FROM vw_IntakeAssessmentAcuityScores
WHERE vw_IntakeAssessmentAcuityScores.ClientID=HIFIS_Clients_HousePlacements.ClientID
AND vw_IntakeAssessmentAcuityScores.AssessmentTool = 'SPDAT'
AND vw_IntakeAssessmentAcuityScores.AssessmentToolType NOT LIKE '%VI%'
AND vw_IntakeAssessmentAcuityScores.IntakeDate BETWEEN DATEADD(month,-6,HIFIS_HousePlacements.MovedInDate) AND HIFIS_HousePlacements.MovedInDate
ORDER BY IntakeID DESC
)

当我这样做时，我得到了错误The multi-part identifier HIFIS_HousePlacements.MovedInDate cannot be bound

因此，尽管我知道我没有在子查询中定义 HIFIS_HousePlacements，但 SQL 表达式字段的规则表明我不应该这样做。谁能帮助我了解如何解决此错误？

标签： sqlsql-servercrystal-reports

解决方案

我已经解决了部分问题。

部分问题取决于方程边的顺序。

所以我确定这会导致错误：

(
SELECT TOP 1 AssessmentAcuityType
FROM vw_IntakeAssessmentAcuityScores
WHERE vw_IntakeAssessmentAcuityScores.ClientID=HIFIS_Clients_HousePlacements.ClientID
)

而这并不

(
SELECT TOP 1 AssessmentAcuityType
FROM vw_IntakeAssessmentAcuityScores
WHERE HIFIS_Clients_HousePlacements.ClientID=vw_IntakeAssessmentAcuityScores.ClientID
)

推荐阅读