I get error when creating cohorts.

[jepark315]

I'm pretty sure this error is thrown because I use oracle instead of ms sql. The error report says that "COHORT_NUMBER" is an invalid identifier so I wanted to locate this and change it to the some other word, but couldn't find it.

I wonder where this "Contructing target and comparator cohorts" message comes from (from which .R files or .sql files?)

I wonder if you could give me a hint or any ideas to fix it.

Thank you!

errorReport_SCYOU.txt

[jepark315]

Sure!

DBMS: oracle

Error: java.sql.SQLSyntaxErrorException: ORA-00904: "COHORT_NUMBER": 부적합한 식별자

SQL: CREATE TABLE SNUBH_RESULTS.fhjj0djjcohort_person AS SELECT ROW_NUMBER() OVER (ORDER BY person_id, cohort_start_date) AS row_id, subject_id,

cohort_definition_id,

cohort_start_date,
(CAST(cohort_start_date AS DATE) - CAST(observation_period_start_date AS DATE)) AS days_from_obs_start,

    (CAST(cohort_end_date AS DATE) - CAST(cohort_start_date AS DATE))
 AS days_to_cohort_end,

    (CAST(observation_period_end_date AS DATE) - CAST(cohort_start_date AS DATE))
 AS days_to_obs_end,
cohort_end_date

FROM (SELECT subject_id, cohort_definition_id, cohort_start_date, cohort_end_date FROM (SELECT subject_id, cohort_definition_id, MIN(cohort_start_date) AS cohort_start_date, MIN(cohort_end_date) AS cohort_end_date FROM (SELECT subject_id, cohort_definition_id, cohort_start_date, cohort_end_date, ROW_NUMBER() OVER (PARTITION BY subject_id ORDER BY cohort_start_date) AS cohort_number FROM (SELECT exposure_table.subject_id,

    cohort_definition_id,

    cohort_start_date,
    cohort_end_date
FROM SNUBH_RESULTS.my_cohorts exposure_table

          WHERE cohort_definition_id IN (874, 929)

) temp WHERE cohort_number = 1

 ) raw_cohorts

GROUP BY subject_id, cohort_definition_id ) first_only

WHERE cohort_start_date >= (SELECT MAX(start_date)
            FROM (SELECT MIN(cohort_start_date) AS start_date
                FROM SNUBH_RESULTS.my_cohorts

                  WHERE cohort_definition_id IN (874, 929)
                GROUP BY cohort_definition_id

             ) tmp
             )
AND cohort_start_date <= (SELECT MIN(end_date)
            FROM (SELECT MAX(cohort_start_date) AS end_date
                FROM SNUBH_RESULTS.my_cohorts

                  WHERE cohort_definition_id IN (874, 929)
                GROUP BY cohort_definition_id

             ) tmp
             )
 ) common_period

INNER JOIN CDM.observation_period ON subject_id = person_id WHERE cohort_start_date <= observation_period_end_date AND cohort_start_date >= observation_period_start_date AND cohort_start_date >= CAST('20130301' AS DATE)

R version: R version 3.4.2 (2017-09-28)

Platform: x86_64-w64-mingw32

Attached base packages:

• stats
• graphics
• grDevices
• utils
• datasets
• methods
• base

Other attached packages:

• TicagrelorVsClopidogrel (0.0.1)
• MethodEvaluation (0.3.1)
• EmpiricalCalibration (1.3.6)
• CohortMethod (3.0.1)
• Cyclops (2.0.1)
• FeatureExtraction (2.1.5)
• OhdsiSharing (0.1.3)
• DatabaseConnector (2.2.1)
• SqlRender (1.5.3)

[chandryou]

I'm sorry. Could you update CohortMethod (3.0.1) to (3.0.2) version and try again? I think this issue can be solved in v3.0.2 throug this thread