OHDSI Home | Forums | Wiki | Github

Achilles on SQL Server DB with case sensitivity


(Dave Barman) #1

Hello,

I am testing Achilles against a CDM in SQL Server. The database is configured as case sensitive. A number of the tests fail on this sort of error. You will see below that there is a mixture of cases for person_id (PERSON_ID vs person_id).

I am wondering if anyone has encountered this error if anyone is aware of a workaround.

Thanks!
Dave

DBMS:
sql server

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘PERSON_ID’.

SQL:
–HINT DISTRIBUTE_ON_KEY(stratum_1)
WITH rawData AS (
select
de1.drug_concept_id as stratum_1,
YEAR(drug_era_start_date) as stratum_2,
p1.gender_concept_id as stratum_3,
floor((year(drug_era_start_date) - p1.year_of_birth)/10) as stratum_4,
COUNT_BIG(distinct p1.PERSON_ID) as count_value
from OMOPCDM.person p1
inner join
OMOPCDM.drug_era de1
on p1.person_id = de1.person_id
group by de1.drug_concept_id,
YEAR(drug_era_start_date),
p1.gender_concept_id,
floor((year(drug_era_start_date) - p1.year_of_birth)/10)
)
SELECT
904 as analysis_id,
CAST(stratum_1 AS VARCHAR(255)) as stratum_1,
cast(stratum_2 as varchar(255)) as stratum_2,
cast(stratum_3 as varchar(255)) as stratum_3,
cast(stratum_4 as varchar(255)) as stratum_4,
cast(null as varchar(255)) as stratum_5,
count_value
into #s_tmpach_904
FROM rawData

R version:
R version 3.5.3 (2019-03-11)


t