{"id":74,"date":"2017-10-23T19:28:54","date_gmt":"2017-10-23T19:28:54","guid":{"rendered":"http:\/\/eipsoftware.com\/musings\/?p=74"},"modified":"2018-02-19T16:39:50","modified_gmt":"2018-02-19T16:39:50","slug":"effective-use-of-collate","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/effective-use-of-collate\/","title":{"rendered":"Effective use of Collate"},"content":{"rendered":"

\u201cTalk about reaching over your shoulder to scratch the back of your knee\u201d<\/em> — Dave M.<\/p>\n

My former co-worker Dave was full of such colorful phrases, and this was one of his favorites. He would use the phrase anytime he saw someone using an inefficient method to accomplish a task.<\/p>\n

In this vein, I am going to attempt to share some tips, tricks, and helpful hints about how to accomplish things quicker and easier. Some of you out there may be already aware of these tips, but others may not. Also, if you have some hints to share send them along, so everyone can benefit.<\/p>\n

How to Collate<\/h4>\n

<\/p>\n

When I first joined Coca-Cola I started to learn about all of the wonderful idiosyncrasies of BASIS. BASIS is an ERP system created before anyone knew what an ERP system was. BASIS uses flat files to store its data. I think BASIS was designed for the IBM 360 system, the software was created long before I ever joined the organization.\u00a0 And I observed a wide variety of methods for dealing with BASIS\u2019s idiosyncrasies. BASIS has a finite number of codes available for different fields. Many times the field length is only a single character. And because BASIS limits input to numbers and upper and lower case Latin alphabet, it gives a max number of values as 62. (10 numbers + 26 upper case letters + 26 lower case letters)<\/p>\n

Joy oh Joy.\u00a0<\/strong><\/p>\n

On a nightly basis, the extraction process takes a snapshot of the BASIS tables and moves them into the\u00a0 Microsoft SQL server. When the Microsoft SQL database was setup the tables were designated as case insensitive. Case insensitive means if you search for a value with the SQL statement<\/p>\n

WHERE<\/span><\/span><\/span> am01_artgrp6 <\/span><\/span>=<\/span><\/span><\/span> ‘b’<\/span><\/span><\/span><\/p>\n

the results would actually return rows where<\/p>\n

am01_artgrp6 <\/span><\/span>=<\/span><\/span><\/span> ‘b’ <\/span><\/span><\/span><\/p>\n

or<\/span><\/span><\/p>\n

am01_artgrp6 <\/span><\/span>=<\/span><\/span><\/span> ‘B’<\/span><\/span><\/span><\/p>\n

In the BASIS table ‘b’is very different from ‘B’. So the question is, how do we write a query to only get am01_artgrp6 = ‘b’?<\/p>\n

Effective use of Collate<\/h4>\n

I have seen a couple of different ways being used but all of these methods ignored SQL\u2019s built in mechanism for handling the problem.<\/p>\n

T-SQL supports the SQL statement COLLATE.<\/p>\n

The COLLATE statement allows you to override the default collation of the table. The tables on our SQL server were set up as case insensitive. When you use the COLLATE statement you can tell the SQL server that in your query your SQL parameters are case sensitive. By making your parameters case sensitive, now you can return results only where am01_artgrp6 = ‘b’.<\/p>\n

Example:<\/strong><\/p>\n

-- ---------------------------------------------------------------------------\r\n-- Count number of articles, grouped by BASIS container code.\r\n-- ---------------------------------------------------------------------------\r\n\r\nSELECT\t\tb.am01_artgrp6 COLLATE\t SQL_Latin1_General_Cp1251_CS_AS  AS basis_container \r\n\t\t    ,COUNT(b.am01_artnum)\t\t\t\t\t  AS count_basis_article_number\r\n\t\t\t\r\nFROM\t\tdgBasis.dbo.AM01 AS b\r\n\t\t\t\t\r\nWHERE\t\tb.am01_artnum > 0\r\n\r\nGROUP BY\tb.am01_artgrp6 COLLATE\tSQL_Latin1_General_Cp1251_CS_AS<\/pre>\n
 <\/p>\n
Explanation:<\/strong><\/p>\n
You can see I am doing a simple count query and I am going to group on am01_artgrp6.<\/p>\n
By adding the COLLATE SQL_Latin1_General_Cp1251_CS_AS statement I am overriding the default collation. When I override the default collation, I then need to tell the SQL server what collation I would like to use. In the example I want to use a case sensitive collation, based on the Latin alphabet.<\/p>\n
In this case I want to stay with Latin 1 general,<\/p>\n
CP1251 \u2013 Microsoft windows implementation of Latin 1 alphabet<\/p>\n
CS \u2013 Case sensitive<\/p>\n
AS \u2013 Alpha sort.<\/p>\n
Extra Info<\/strong><\/p>\n
Microsoft SQL has dozens of collations defined for different languages, non-Latin based character sets, and right to left languages. Below is a quick query that lists all of the collations installed on the server with its corresponding description.<\/p>\n
-- ----------------------------------------------------------------------------\r\n-- List all available collations\r\n-- ----------------------------------------------------------------------------\r\nSELECT *\r\nFROM fn_helpcollations()<\/pre>\n
 <\/p>\n
In the future, if you have to work with a table that is marked as case insensitive, however you do care about the case sensitivity the above code samples should make your code simpler, and your queries run faster.<\/p>\n","protected":false},"excerpt":{"rendered":"
\u201cTalk about reaching over your shoulder to scratch the back of your knee\u201d — Dave M. My former co-worker Dave was full of such colorful phrases, and this was one of his favorites. He would use the phrase anytime he saw someone using an inefficient method to accomplish a task. In this vein, I am […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,4,6],"tags":[13,14,15,16],"series":[],"class_list":["post-74","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-code","category-sql","tag-hint","tag-sql","tag-t-sql","tag-collate"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/74","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=74"}],"version-history":[{"count":9,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/74\/revisions"}],"predecessor-version":[{"id":148,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/74\/revisions\/148"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=74"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=74"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=74"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=74"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}