Written by

UKK Köln
Question Dmitrii Baranov · Nov 17, 2024

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
Product version: IRIS 2024.1

Comments

Dmitrii Baranov  Nov 18, 2024 to Timo Lindenschmid

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

0