| November 14, 2018

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

Comment wrap

Swati Rajput

Swati Rajput is an IBM Certified Big Data Architect working as a Technical Analyst and a Client Advocate for the Marketing Suite at HCL.

Topics in this article:

Campaign Version 11.0

18 Comments

Salim on January 10, 2019 at 7:32 am

It’s really a great and useful piece of information. I am glad that you shared this useful info with us. Please stay us up to date like this. Thank you for sharing.
Good Luck Swati
Reply
- Swati on January 10, 2019 at 7:46 pm
  
  Thank You
Abhishek Kumar Singh on January 10, 2019 at 9:57 am

Hi Swati,
Good peice of information!!
Reply
- Swati on January 10, 2019 at 7:47 pm
  
  Thanks Abhishek
Amarjeet Singh on January 10, 2019 at 9:59 am

Thanks for sharing this information Swati ??
Reply
- Swati on January 10, 2019 at 7:47 pm
  
  Thanks Amarjeet
Sourav Dutta on January 10, 2019 at 11:12 am

This was a good read ! Another nice scenario discussed here .. Thanks for the information Swati
Reply
- Swati on January 10, 2019 at 7:48 pm
  
  Thank You Saurav
Viren Upreti on January 10, 2019 at 6:53 pm

Very useful..
Reply
- Swati on January 10, 2019 at 7:48 pm
  
  Thanks Viren
Shivangi on January 10, 2019 at 7:46 pm

It’s very helpful.
Reply
- Swati on January 10, 2019 at 7:49 pm
  
  Thanks Shivangi
Ajay on January 10, 2019 at 7:56 pm

Useful information.
Reply
- SWATI RAJPUT on January 10, 2019 at 7:56 pm
  
  Thanks Ajay
Alok on January 10, 2019 at 8:09 pm

Good information Swati.
Reply
Raksha on January 10, 2019 at 8:19 pm

Good piece of info swati
Reply
Manmeet Chauhan on January 10, 2019 at 8:43 pm

Good stuff Swati.. very useful information.
Reply
Abheek on January 10, 2019 at 9:32 pm

Interesting article. We encounter several errors like these but sometimes it is not easy to identify the root cause. This is really an useful piece of information. Thank you.
Reply

Submit a Comment Cancel reply

Further Reading

unica

Marketing & Commerce | August 6, 2020

Offer Creation Using Unica Centralized Offer Management (COM)

Why is offer management improved when it is centralized? Marketers need to have control over offer distribution on the marketing channels.

Lalitkumar Dudhe

Unica Technical Support Consultant

unica

Marketing & Commerce | July 6, 2020

Let’s Geek Out on Unica: Scaling Your Campaign Execution

Everyone is trying to do more with less – quickly. I have clients who ask me all the time “How do I makes things faster?” or “How do I streamline campaign execution while doing more campaigns?” Both valid questions. Folks want to execute at scale, do more personalization, and run more campaigns in more channels simultaneously. This is where the magic that is Unica can come to the rescue.