如何正确构建此整理？

The following code:

以下代码:

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'   ) 

Is throwing the following error:

抛出以下错误:

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.

消息468,级别16,状态9,行1无法解决等于操作中“Latin1_General_CI_AS”和“Latin1_General_CS_AS”之间的排序规则冲突。

I've tried to fix this with the following, but I'm receiving a syntax error. I'm at a loss (even after looking at documention) how this should be done in this instance:

我试着通过以下方法解决这个问题,但是我收到了语法错误。我不知道(即使在查看文档之后)在这种情况下应该如何做到这一点:

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'   ) 

1 个解决方案

#1

Possibly 2 issues with your query. Try something like this

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

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

You may need to add that same syntax to your openquery command. This is where I'm not sure if it will work as you may need to use the linkedserver.database.schema.table syntax -- just need to test it. But something like this:

您可能需要将相同的语法添加到openquery命令。这是我不确定它是否会工作的地方,因为您可能需要使用linkedserver.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

Good luck.