My view is being consumed via microstrategy and when they choose a value from a PROMPT it is a standard SQL statement and if the user typed lower case then that's what is passed. Yes that's more what I was looking for a global setting as you touched upon. Supposedly when they have to write custom SQL in their prompts for example (like to select only UCASE using my workaround) they then lose other functionality with their prompts. Perhaps I am misunderstanding your comment? My workaround for the user is to use UCASE in their select statement but they are asking if we can turn case sensitivity off at the HANA database level due to restrictions in the functionality of the Microstrategy software they are using. I ALREADY have all caps in my column and my PLANT (werks) is stored in the column as 'MY01' but the user is searching using just lower case (ie: they are searching for 'my01' and no result). The problem I'm having though is actually the opposite. It seems your example is creating a new column with the new entries inserted as lower case (ie: insert into bbb values ('Lars') ) and then they are actually stored upper case in the new column. Lars, I'm just re-reading this forum and specifically stuck on your comment about the generated columns. The huge difference in SAP HANA compared to row oriented DBMS is that the evaluation of the WHERE condition only works on the dictionary (the unique values of each column) and not on every single row. If this provides a much better performance is something to really test with actual data. So, the old tricks still work with SAP HANA. Select * from bbb where upper(name) like '%AR%' NAMEĮXPLAIN PLAN FOR select * from bbb where upper(name) like '%AR%' ĬOLUMN TABLE FILTER CONDITION: BBB.UPPER_NAME LIKE ''%AR%'' Now, for the use of LIKE instead of equal, the feature works as well: , upper_name varchar(20) generated always as UPPER(name)) ĮXPLAIN PLAN FOR select * from bbb where upper(name) = upper('LaRs') ĬOLUMN SEARCH BBB.NAME, BBB.UPPER_NAME (LATE MATERIALIZATION)ĬOLUMN TABLE FILTER CONDITION: BBB.UPPER_NAME = 'LARS'Īs we see, the generated column is used here without any re-coding of the SQL statement. In SAP HANA the SQL optimizer is clever enough to figure out that there already is a pre-comuputed upper case version of the data in this case:Ĭreate column table bbb (name varchar(20) It's pretty similar to generated columns in SAP HANA and would allow the optimizer to leverage an existing index structure. In addition to Stefans comment, the classic workaround for this requirement would be to create what Oracle calls a Function Based Index.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |