OHDSI Home | Forums | Wiki | Github

Circe Generate

Developers
#1

Hi @Chris_Knoll,

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.

#2

Ok, I’ve replaced Raw with RawEvents. Let me know if that fixes it for you.

-Chris

#3

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

#4

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

#5

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.

#6

Thanks!

#7

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

Let me know if you have any concerns.

-Chris

#8

Could you fix the oracle profile when you make the change? That would be the main problem, since it currently has them concatenated.

#9

I will do that.

-Chris

#10

I have another issue report on Circe

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
];

I wonder if you could fix this.

Thanks.
Min

#11

This looks like it could be comming from a vocabulary query? @Frank or @schuemie, do you know? ISNULL is supported by postgres?

-Chris

#12

Is it NULLIF() in Postgres?

#13

http://www.postgresql.org/docs/9.1/static/functions-comparison.html

expression ISNULL
expression NOTNULL

#14

ISNULL(arg1, arg2) is not supported by postgresql but MSSQL

#15

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?

#16

The error occurs in the popup window of “select concept”, no tab of “show sql” can be found, I attached a screenshot
of demo in the ohdsi website.

t