When creating a join based on strings that are case-sensitive, use Make Query Table with COLLATE. For example, the Expression parameter for Make Query Table should look something like the following, where dataset1.field is joined to dataset2.field:
Case sensative join in SQL Server
Download File: https://geags.com/2vJirr
There is an older knowledge base article that suggests to directly modify the column to use case-sensitive collation: HowTo: Enable case-sensitive queries on an attribute column in ArcSDE for SQL Server. This workflow may not work however, as the article is outdated. If the workflow outlined in the above article does not work, the ALTER TABLE command may be used instead using the following steps. The advantage to this workflow is that joins may be created as they normally would, without calling COLLATE and without using Make Query Table. The disadvantage is that the columns must be directly modified.
Since I want to join to get the correct TenantID for an AR report, I would want to make sure I am always bringing in the correct tenant. If the case is different, is there anything I can write to differentiate a situation like John's Shop?
For a case-sensitive join on the CLERK field, do I need to specify the collation in the join clause as well, or does the fact that it is specified at the DDL level mean that it isn't needed in the join?
I have a question. I use proc sql left join to compare the two tables below. However, I found the uppercase in Name of &state.outname is not recognized by Var of Dct. For example, the string 'CODE_CO' in Name of table '&state.name' are actually the same as 'Code_co' in Var of Table 'Dct'. Please advide how to fix this probelm. Thank you!
In this case, where ALL varchar fields are used for upper-/lowercase text entry and all of them are used for filtering/joining, then I'd tend to say: the application needs to be adapted.Anyhow, good luck with this.Cheers, Lars\",\"author\":\"username\":\"lars.breddemann\",\"displayName\":\"Lars Breddemann\",\"groupIcons\":[\"name\":\"tech-ambassador\",\"title\":\"This user is a Tech Ambassador\",\"priority\":16,\"name\":\"mentor-alumni\",\"title\":\"This user is an SAP Mentor Alumnus\",\"priority\":17,\"name\":\"employee\",\"title\":\"This user is an SAP Employee\",\"priority\":21],\"suspended\":false,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"id\":11293919,\"creationDate\":1414061434000,\"activeRevisionId\":11782120,\"lastActivity\":1414061436000,\"parentId\":11293364,\"originalParentId\":11291269,\"likeCount\":0,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false}]},\"11292062\":\"rootParentId\":11292062,\"commentsCount\":1,\"comments\":[\"body\":\"Hi,\\ni read this treed but don't help because CONTAINS Function don't work on sub query columns, and i have to do alot work to manage these case that on SQL are free. \\ni did think that there was something that help this kind of problem since SAP UI do this.\\n\\n\\nregards\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"patrickbachmann\",\"displayName\":\"Patrick Bachmann\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":11292252,\"creationDate\":1413989994000,\"activeRevisionId\":11780749,\"lastActivity\":1413989998000,\"parentId\":11292062,\"originalParentId\":11291269,\"likeCount\":0,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false]}"); const simplifiedQuestionView = JSON.parse("true"); (function() window.pageContext = mergeDeep(pageContext, question: id: 11291269, plug: "query-hana-sensitive-case-big-problem", votes: 0, questionTitle: "query hana sensitive case big problem!!!!!!", isClosed: false, isLocked: false, isRedirected: false, redirectedFromTitle: "", redirectedFromId: "", closedStatusData: JSON.parse(""), userVoted: false, relations: JSON.parse("\"canClose\":false,\"canUnredirect\":false,\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"canReopen\":false,\"type\":\"question\",\"canVoteUpOrCancel\":false,\"canViewRevisions\":true,\"canUnlock\":false,\"reported\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canCancelReport\":false,\"canComment\":true,\"isCurrentUserAuthor\":false,\"canViewReports\":false"), isQuestionAccepted: false , childToViewInfo: id: "" , comments: JSON.parse("\"11292665\":\"rootParentId\":11292665,\"commentsCount\":4,\"comments\":[\"body\":\"In this case, where ALL varchar fields are used for upper-/lowercase text entry and all of them are used for filtering/joining, then I'd tend to say: the application needs to be adapted.Anyhow, good luck with this.Cheers, Lars\",\"author\":\"username\":\"lars.breddemann\",\"displayName\":\"Lars Breddemann\",\"groupIcons\":[\"name\":\"tech-ambassador\",\"title\":\"This user is a Tech Ambassador\",\"priority\":16,\"name\":\"mentor-alumni\",\"title\":\"This user is an SAP Mentor Alumnus\",\"priority\":17,\"name\":\"employee\",\"title\":\"This user is an SAP Employee\",\"priority\":21],\"suspended\":false,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"id\":11293919,\"creationDate\":1414061434000,\"activeRevisionId\":11782120,\"lastActivity\":1414061436000,\"parentId\":11293364,\"originalParentId\":11291269,\"likeCount\":0,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false],\"11292062\":\"rootParentId\":11292062,\"commentsCount\":1,\"comments\":[\"body\":\"Hi,\\ni read this treed but don't help because CONTAINS Function don't work on sub query columns, and i have to do alot work to manage these case that on SQL are free. \\ni did think that there was something that help this kind of problem since SAP UI do this.\\n\\n\\nregards\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"patrickbachmann\",\"displayName\":\"Patrick Bachmann\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":11292252,\"creationDate\":1413989994000,\"activeRevisionId\":11780749,\"lastActivity\":1413989998000,\"parentId\":11292062,\"originalParentId\":11291269,\"likeCount\":0,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false]"), answerPager: answersCount: 2, page: 1, pageSize: 10, pageCount: 1, sort: "votes" , answers: JSON.parse("[\"body\":\"First off: please don't ever start saving on exclamation marks. They really cost nothing and are so super effective in attracting attention and underlining that you really have a serious and very important - if not life changing - request.Now to your question:Yes, MS SQL Server allows for case-agnostic WHERE conditions and other DBMS, like SAP HANA, don't. It's a MSS specific feature, not a SQL Standard function, so that should have been part of your migration project.Since you're already a step further here, I would propose to focus on an approach that allows to postpone large code changes.You could, e.g. 1. update all column to a uniform case and2. setup triggers (yes, there are situations where I consider them useful and appropriate) that ensure that the columns are never again filled with the wrong case.That'll get you over the next weeks while you can refactor the db access code in your application.Cheers,Larsp.s. next time WRITING EVERYTHING IN UPPER CASE LETTER (how nicely this fits to the topic of this thread) WILL GET YOU TO THE ANSWERS EVEN FASTER.\",\"author\":\"username\":\"lars.breddemann\",\"displayName\":\"Lars Breddemann\",\"groupIcons\":[\"name\":\"tech-ambassador\",\"title\":\"This user is a Tech Ambassador\",\"priority\":16,\"name\":\"mentor-alumni\",\"title\":\"This user is an SAP Mentor Alumnus\",\"priority\":17,\"name\":\"employee\",\"title\":\"This user is an SAP Employee\",\"priority\":21],\"suspended\":false,\"isCurrentUser\":false,\"id\":11292665,\"posted\":1414005272000,\"votes\":2,\"isAccepted\":false,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"score\":2,\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[],\"body\":\"Hi Alessandro. Please see my previous post with similar issue;-Patrick\",\"author\":\"username\":\"patrickbachmann\",\"displayName\":\"Patrick Bachmann\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":11292062,\"posted\":1413985026000,\"votes\":0,\"isAccepted\":false,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[]]"), answerForm: formAction: "/answers/11291269/post.json", textareaName: "body", textareaErrors: "", isAttachmentsEnabled: true, answerEditorialGuideline: title: "Before answering", content: "You should only submit an answer when you are proposing a solution to the poster\'s problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that your answer complies with our Rules of Engagement.", links: [ title: "Rules of Engagement", href: " -of-engagement.html", ] , answerMinBodyLength: '10', answerMaxBodyLength: '20000' , currentUser: sapInternalId: '', permissions: canVoteUpOrCancel: false, canVoteDownOrCancel: false, canModerate: false, , isVotedUp: false, isVotedDown: false , alerts: alertModeratorMinLength : "It should be given a proper explanation about why the content is inappropriate.", alertModeratorMinLengthValue : "10", alreadyReportedMessage : "You already have an active moderator alert for this content." , url: profileApiBaseUrl: ' -api.services.sap.com', followUnfollowQuestion: '/sap/nodeSubscription.json', isFollowingQuestion: '/sap/isFollowingNode.json', vote: voteUp: '/commands/0/voteup.json', voteDown: '/commands/0/votedown.json', cancelVote: '/commands/0/cancelvote.json' , rss: answers: '/feed/11291269/answers.rss', answersAndComments: '/feed/11291269/comments-and-answers.rss' , authorizeUploadContext: type: 'answer' , atMention: userSearchServiceUrl: ' ', currentUserName: '', useNewUSSCORS: true, atMentionDelayMs: 100, showMentionInRedactor: true , attachmentSettings: commentMaxAttachments: '2', answerMaxAttachments: '10', commentMaxAttachmentSizeBytes: '1048576', answerMaxAttachmentSizeBytes: '1048576', commentAttachmentsSizeBytesTotal: '2097152', answerAttachmentsSizeBytesTotal: '10485760' , editor: editorClipboardUploadEnabled: true ) )(); Home
Community
Ask a Question
Write a Blog Post
Login / Sign-up Search Questions and Answers 0 Former Member Oct 22, 2014 at 09:34 AM query hana sensitive case big problem!!!!!! 1189 Views Follow RSS Feed Hi all, 2ff7e9595c
Comentários