SQL grouping, values converted to uppercase, unicode symbols lost
I'm playing with some anayltic queries against FHIR server tables. The HSFHIR_X0002_S_Patient.addressCity table contains a lot of cities which names contain german charachers such as ä, ö and ü.
The following query works fine:
select value from HSFHIR_X0002_S_Patient.addressCity
But this one converts city names to uppercase, and characters with umlauts are lost, so instead of "Köln" or "München" I see KOLN and MUNCHEN:
select ac.value, count(ac.value) as cnt
from HSFHIR_X0002_S_Patient.addressCity ac
group by ac.value
order by 2 desc
I'm using DBeaver with IRIS official JDBC driver.
UPD: distinct also makes city names malformed:
select distinct(value) from HSFHIR_X0002_S_Patient.addressCity
Comments
Hi, 2 options:
Is the locale set to DE in Iris, and have a look at this option: FastDistinct | Configuration Parameter File Reference | InterSystems IRIS Data Platform 2024.2
But most probably its the FastDistinct default setting, you might want to disable that as per documentation.
Hi Timo,and thanks a lot, I've disabled the FastDistinct so now everything works as expected. Your link took me to a page that describes the Group By operator and it happened so this "feature" is documented and that another option is to use the %Exact function, e.g. select distinct %Exact(value) ...