sql - Query Of Queries 运行时错误 - 选择列引用 [X] 不是 FROM 表列表的任何表中的列

Question

我正在尝试对 ColdFusion 查询应用过滤器，但总是出错。这是使用的原始查询：

<cfquery name="qAssociationIdentifierAttributeEncryptedValues" datasource="#variables.dsn#">
    SELECT identifier as attribute_identifiername
        , assoitem.creationdate
        , convert(varchar, month(creationdate)) + '/' + convert(varchar, day(creationdate)) + '/' + convert(varchar, year(creationdate)) as attribute_createdon
        , aap.associationitemid as attribute_identifierid
        , (
            Select DISTINCT u.FirstName + ' ' + u.LastName + '~'  
            FROM associations_users  au                 
            INNER JOIN "User" u ON u.UserID = au.useridFK 
            WHERE au.associationitemidFK = aap.associationitemid
                AND u.usertype = <cfqueryparam value="Volunteer" cfsqltype="cf_sql_varchar">    
                AND u.programid = <cfqueryparam value="#arguments.programid#" cfsqltype="cf_sql_integer">
            FOR XML PATH('') 
          ) AS attribute_assignedto
        , CASE WHEN aap.isactive = 1 THEN 'Active' ELSE 'Archived' END AS attribute_status
        , ( 
            SELECT DISTINCT convert(varchar,u.userid) + '~'
            FROM associations_users au                 
            INNER JOIN "User" u ON u.UserID = au.useridFK 
            WHERE au.associationitemidFK = aap.associationitemid
                AND u.usertype = <cfqueryparam value="Volunteer" cfsqltype="cf_sql_varchar">    
                AND u.programid = <cfqueryparam value="#arguments.programid#" cfsqltype="cf_sql_integer">
            FOR XML PATH('') 
          ) as attribute_assignedtoid
        , ( 
            SELECT u.FirstName + ' ' + u.LastName + '~'  
            FROM associations_users_tracker aut              
            INNER JOIN "User" u ON u.UserID = aut.useridFK               
            WHERE aut.associationitemidFK = aap.associationitemid   
                AND aut.operationPerformed = <cfqueryparam value="D" cfsqltype="cf_sql_varchar">            
                AND u.usertype = <cfqueryparam value="Volunteer" cfsqltype="cf_sql_varchar">    
                AND u.programid = <cfqueryparam value="#arguments.programid#" cfsqltype="cf_sql_integer">
            ORDER BY u.LastName,u.FirstName
            FOR XML PATH('') 
          ) as attribute_historicalassignedto
        , (
            SELECT convert(varchar,u.userid) + '~'  
            FROM associations_users_tracker AS aut                  
            INNER JOIN "User" u ON u.UserID = aut.useridFK               
            WHERE aut.associationitemidFK = aap.associationitemid
                AND aut.operationPerformed = <cfqueryparam value="D" cfsqltype="cf_sql_varchar">                
                AND u.usertype = <cfqueryparam value="Volunteer" cfsqltype="cf_sql_varchar">    
                AND u.programid = <cfqueryparam value="#arguments.programid#" cfsqltype="cf_sql_integer">
            ORDER BY OperationDate,u.LastName,u.FirstName
            FOR XML PATH('') 
          ) as attribute_historicalassignedtoid
        , associationid                           
    FROM association_attribute_#arguments.programid# aap, association_items assoitem             
    WHERE assoitem.associationitemid = aap.associationitemid
        AND associationid = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.associationID#">
</cfquery>

每次我得到查询运行时错误的列引用查询。即使列名存在于查询中。

查询查询运行时错误。选择列引用Query Of Queries 运行时错误。选择列引用 [attribute_2111] 不是 FROM 表列表的任何表中的列。

这是我正在使用的子查询：

<cfquery name="qAssociationIdentifierAttributeValuesinParts" dbtype="query">
    Select * from qAssociationIdentifierAttributeEncryptedValues        
    Where #ColumnNameFromDBQuery# = #Value#
    ORDER BY attribute_identifiername           
</cfquery>

请建议。

标签： sqlcoldfusion