Binary Sort Collation Campaign
Today, I am writing about how different binary collations between databases affect Campaign sorting and result in an error. The 'Campaign' uses binary sort collation but this is not enough. I encountered a case where two databases (Oracle and Netezza) are using binary sort collation but still, it results in incorrect sort order. In the case, the input is taken from 1 database and this was made output to another database. ERROR Message encountered : [SORT ORDER] [THIS_CYCLE] Audience ID: Wrong sort order detected (Last ID > This ID)[SORT ORDER] [THIS_CYCLE] Audience ID: Wrong sort order detected (Last ID > This ID)Last ID: Name=FK_ROWID_ASSET, Type=8, Chars=100, Bytes=100, dbtype=17, prec=0, Value='02i0Y000001XOBiQAO' Reason: This is because sort order of database is different from what Campaign expects. Although both databases have binary sort collation yet there is a difference between these two binary sort collations itself. One database is using ASCII format and the other one is using EBCDIC standard. In the ASCII standard, all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters. So, the way one database sorts are different from the way another database expects sorting to be. Hence it resorts them onto campaign server. That's why we get the wrong sort order error. If this is the case, there is no way we can avoid this error. This is as expected. Documents referred: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/linguistic-sorting-and-matching.html#GUID-68633595-DC95-4393-A6B8-146AD05F5FDF https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/charset-binary-collations.html Swati Rajput I am a Technical Analyst with over 3+ years of experience in the IT and software industry, with a focus to help my clients do better and achieve better customer satisfaction. I am currently a Technical Analyst for Marketing Suite at HCL.