Jon wanted me to let you know the error we were having with oracle on generating cohorts from Circe. I think it’s this:
INSERT INTO @targetTable (cohort_definition_id, subject_id, cohort_start_date, cohort_end_date)
select @cohortDefinitionId as cohort_definition_id, person_id as subject_id, start_date as cohort_start_date, end_date as cohort_end_date
FROM
(
select Raw.*, row_number() over (partition by Raw.person_id order by Raw.start_date ASC) as ordinal
FROM
(
select person_id, start_date, end_date
FROM pbshy0m3PrimaryCriteriaEvents
) Raw
) Results
;
Because raw is a datatype in Oracle, I think that’s what’s causing a problem.
One mroe thing: there’s a new token in application.properties called cohort.targetTable (replacable by the POM.xml token cohort.targetTable). This should be the schema and tablename Cohort in your ohdsi schema (so {ohdsischema}.cohort). I’m pointing this out because i see the @targetTable token in that sql snipit you quoted.
Chris,
It was great meeting you at the f2f. Chris Leng, Tao He and I would like to join the CIRCE team development meetings. We think this is a product we would like to work with and contribute too. Can you include us in the distribution list.
brian.sauer@utah.edu
chris.leng@utah.edu
tao.he@utah.edu
Ok, I’ve added those to the mailing list, you should have recieved the meeting invite. I will post over on the general forum the webex details for anyone who cares to join.
@cahilton, I’m working on a branch for some sql query optimization, and i’ve been asked to standardize the replace tokens in the sql. The @targetTable is one case above, and any cdm table reference will be prefixed with @cdm_database_schema. I wanted to make sure that you were aware of this, and wanted to know if this had any impact on you. The @targetTable token will be replaced with two tokens: @target_database_schema and @target_table, and will appear in the template as @target_database_schema.@target_table. I will pull the target_database_schema value from the OHDSI_Scema and the @targetTable I will hardcode as ‘cohort’ in webAPI. This should eliminate the need for the cohort.targetTable application property in pom.xml.
When I was trying to add a concept set
the following error was raised since Postgresql do support function “isnull”
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,‘N’) STANDARD_CONCEPT, ISNULL(INVALID_REASON,‘V’) INVALID_REASON, CONCEPT_CODE, CONCEPT_CLASS_ID, DOMAIN_ID, VOCABULARY_ID
from public.CONCEPT
where (LOWER(CONCEPT_NAME) LIKE ‘%diabetes%’ or CONCEPT_CODE LIKE ‘%diabetes%’)
AND DOMAIN_ID IN (‘Condition’) AND INVALID_REASON IS NULL
order by CONCEPT_NAME ASC
];
In the CIRCE app, you can click show sql, it will bring up a dialog with mulitple tabs, for sql server, oracle, and postgres. Can you go into the UI, click show sql, and go to the postgres tab and see if the IS NULL is still there?
website.