relation eligibles does not exist & invalid input syntax for integer: "2e+06" - CreateEvaluationCohort

[SSMK-wq]

Hi,

I encountered another error while I was trying to execute the CreateEvaluationCohort function.

Please find the error report shown below

`DBMS: postgresql

Error: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "2e+06" Position: 993

SQL:

insert into temp.test_cohort0448834563139826 (COHORT_DEFINITION_ID, SUBJECT_ID, COHORT_START_DATE, COHORT_END_DATE)
 (select 0 as COHORT_DEFINITION_ID, person_id as SUBJECT_ID, (visit_start_date + 0*INTERVAL'1 day') COHORT_START_DATE,
            (visit_start_date + 1*INTERVAL'1 day') COHORT_END_DATE
      from (select  co.subject_id as person_id, co.COHORT_START_DATE as visit_start_date,
                        row_number() over (order by MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)) rn
                    from results.cohort co
                    join cdm.person p
                      on co.subject_id = p.person_id
                        and  EXTRACT(YEAR FROM co.COHORT_START_DATE) - year_of_birth >= -500
                        and EXTRACT(YEAR FROM co.COHORT_START_DATE) - year_of_birth <= 1000
                        and gender_concept_id in (8507,8532)
    #error is here          join eligibles v5 --include only subjects with a visit in their record and within date range
                        on co.subject_id = v5.person_id
                    where co.cohort_definition_id = 105

                        ) negs
     #error is here where rn <= cast('2e+06' as bigint)

    union
      select 0 as COHORT_DEFINITION_ID, SUBJECT_ID, o.observation_period_start_date COHORT_START_DATE,
        (o.observation_period_start_date + 1*INTERVAL'1 day') COHORT_END_DATE
      from cohort_person cp
      join cdm.observation_period o
        on cp.SUBJECT_ID = o.person_id
          and cp.COHORT_START_DATE >= o.observation_period_start_date
          and cp.COHORT_START_DATE <= o.observation_period_end_date
      where rn <= 100
      union
      select 103 as COHORT_DEFINITION_ID, SUBJECT_ID, o.observation_period_start_date COHORT_START_DATE,
        (o.observation_period_start_date + 1*INTERVAL'1 day') COHORT_END_DATE
      from cohort_person cp
      join cdm.observation_period o
        on cp.SUBJECT_ID = o.person_id
          and cp.COHORT_START_DATE >= o.observation_period_start_date
          and cp.COHORT_START_DATE <= o.observation_period_end_date
      where rn <= 100
      )

Two issues here

1) Why the row_number(rn) is in exponential form? ex : rn <= cast('2e+06' as bigint). You can see in the query

2) Where is this "eligibles" stored? Under which schema? I don't see it under temp schema. So when I manually tried to execute the above query, I encountered an error as

             `relation "eligibles" does not exist`

Can help us with this?

[jswerdel]

This has been corrected in the latest version (1.0.5). Thank you for finding this error.

[SSMK-wq]

Hi,

This error still exists. I get the same error again. I have downloaded the recent package from github.

Everytime, I run my R code, I repeat the below steps which I believe gets the latest version from Github

install.packages("drat")
drat::addRepo("OHDSI")
install.packages("FeatureExtraction")
install.packages("DatabaseConnector")
install.packages("SqlRender")
install.packages("PatientLevelPrediction")
install.packages("dplyr")
install.packages("stringr")
install.packages("data.table")
install.packages("PheValuator")

library(dplyr)
library(stringr)
library(data.table)
library(PheValuator)
library(SqlRender)
library(DatabaseConnector)
library(FeatureExtraction)
library(PatientLevelPrediction)

SessionInfo() command produces the below output and you can see it is version 1.0.5

Can help us with this please?

[jswerdel]

I have made another change that will hopefully correct this issue. Please re-load the package and test. Please let me know whether your test was successful.

[SSMK-wq]

Yes, this issue can be closed now.