sql - 我如何正确地构建此校对？

Question

以下代码:

SELECT  *
FROM    portal.workflow AS w
        INNER JOIN portal.workflow_type AS wt
            ON w.workflow_type_id = wt.workflow_type_id
WHERE   wt.doc_type IN ('CentreEV', 'Expenditure Voucher')
        AND w.varchar_1 NOT IN (    select f1.BAT_NAME as 'DocumentFile'
                                    from openquery(QICRE_F1, 'select * 
                                                            from dbo.IO_GLF_BAT_CTL_VW 
                                                            where bat_stat = ''S'' ') f1
                                    where f1.USAGE_STAT = 'A'   ) 

抛出以下错误:

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CS_AS" in the equal to operation.

我已尝试使用以下方法解决此问题，但收到语法错误。我不知所措(即使在查看文档之后)在这种情况下应该如何做到这一点:

SELECT  *
FROM    portal.workflow AS w
        INNER JOIN portal.workflow_type AS wt
            ON w.workflow_type_id = wt.workflow_type_id
WHERE   wt.doc_type IN ('CentreEV', 'Expenditure Voucher')
        AND COLLATE Latin1_General_CI_AS w.varchar_1 NOT IN 
                                (   select f1.BAT_NAME as 'DocumentFile'
                                    from openquery(QICRE_F1, 'select * 
                                                            from dbo.IO_GLF_BAT_CTL_VW 
                                                            where bat_stat = ''S'' ') f1
                                    where f1.USAGE_STAT = 'A'   ) 

Answer 1

您的查询可能有 2 个问题。尝试这样的事情

...
WHERE ...
   AND w.varchar_1 COLLATE Latin1_General_CI_AS IN  
...

您可能需要将相同的语法添加到您的 openquery 命令中。这是我不确定它是否可以工作的地方，因为您可能需要使用 linksserver.database.schema.table 语法——只需要测试它。但像这样:

from openquery(QICRE_F1, 'select bat_name COLLATE Latin1_General_CI_AS  
   from dbo.IO_GLF_BAT_CTL_VW 
   where bat_stat = ''S'' ') f1

祝你好运。