从DataStage 转换到SQL SERVER SP注意点

SQL Collate冲突解决

最新推荐文章于 2021-04-10 06:33:44 发布

原创最新推荐文章于 2021-04-10 06:33:44 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

BI 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了如何解决SQL中由于字符集排序规则不一致导致的数据处理问题。特别关注了DataStage ETL工具与存储过程间因Collate设置不同而引发的问题，并提供了解决方案，包括使用特定的Collate设置确保JOIN操作的一致性和创建临时表时指定Collate以避免冲突。

If one column has one lowercase and one uppercase
DataStage ETL is considering them as separate records but the stored procedure is considering them as one record ...
So it may need to use COLLATE statements.

当转换JOIN和LOOKUP时，尤其要注意在where 条件中加上collate Latin1_General_CS_AI，例如：

select * from table A t

where t.column_name collate Latin1_General_CS_AI = 'Contribute'

在SP中用临时表时如果遇到以下问题：

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CS_AS” in the equal to operation.

解决思路：

If the creation of temp table is from DDL without specifying any collation, the temp table will use instance default collation; if temp table is created by “select …into..”, the collation definitions are carried over

参考网址：

http://www.sqlnotes.info/2012/05/07/collation-of-temp-tables/

查看Check Column Collation：

select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name