query hana sensitive case big problem!!!!!!

[Former Member]

Hi all,

i just migrated an addon from SAP - SQL to SAP - HANA and before i migrated the Company's DB.

The problem occur when execute hana query that have in the "WHERE" varchar filed equal at input value ('M' <> 'm') i can insert "UPPER" to solve the problem

so i have to insert UPPER on all varchar field used in a join in a group by etc.

There is a solution to this big problem, my addon is very big and has hundred and hundred of queries.

some one know how help me?

Regards Alessandro.

[lbreddemann]

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 and

2. 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,

Lars

p.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.

[patrickbachmann]

I recently read that sarcasm is good exercise for the brain and was a sign of intelligence.  Of course I can't remember where I read this however.... I wonder how you feel about extra periods....  I rather like them.

[lbreddemann]

Love them... lots ... even more affordable than !!! as no SHIFT key is required

[Former Member]

Thanks Lars,

Nothing of new, i can't trigger all sap varchar fields because all fields are subject to this problem.

i hope that sap hana release something of new on this topic.

I will follow your advice to post thread.

Regards Alessandro

[lbreddemann]

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

[patrickbachmann]

Hi Alessandro.  Please see my previous post with similar issue;

-Patrick

[Former Member]

Hi,

i 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.

i did think that there was something that help this kind of problem since SAP UI do this.

regards