ERROR: column "condition_era_start_date" does not exist

[lofung]

There is an error in such code.

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: column "condition_era_start_date" does not exist
  Position: 501

SQL:
CREATE TEMP TABLE cov_1

AS
SELECT
CAST(ancestor_concept_id AS BIGINT) * 1000 + 212 AS covariate_id,

    cohort_definition_id,
    COUNT(*) AS sum_value

FROM
(
    SELECT DISTINCT ancestor_concept_id,

        cohort_definition_id,
        cohort.subject_id,
        cohort.cohort_start_date

    FROM ohdsi_cerevello_output.cervello_output cohort
    INNER JOIN ohdsiv5_june2021_thin.condition_era
        ON cohort.subject_id = condition_era.person_id
    INNER JOIN groups
        ON condition_concept_id = descendant_concept_id

    WHERE condition_era_start_date <= (cohort.cohort_start_date + 0*INTERVAL'1 day')
                AND condition_era_end_date >= (cohort.cohort_start_date + -30*INTERVAL'1 day')
        AND condition_concept_id != 0

        AND cohort.cohort_definition_id IN (3042)
) temp

GROUP BY cohort_definition_id,
    ancestor_concept_id

R version:
R version 4.0.5 (2021-03-31)

Platform:
x86_64-w64-mingw32

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:

Too bad it is very convoluted I am not too sure where condition_era_start_date comes from.

Also I find it weird in the code - INNER JOIN ohdsiv5_june2021_thin.condition_era while ohdsiv5_june2021_thin is my OHDSI database, I am sure that the table condition_era does not exist. I am not sure how that table would come into the SQL.

[gowthamrao]

Which version of the CDM are you on - if you are on version 6, then it might explain it as https://github.com/OHDSI/CommonDataModel/blob/master/Redshift/OMOP%20CDM%20redshift%20ddl.txt#L712

If you are in version 5.x then it should work https://github.com/OHDSI/CommonDataModel/blob/v5.3.1_fixes/Redshift/OMOP%20CDM%20redshift%20v5_3_1%20ddl.sql#L373

[lofung]

Oh I see. Let me recheck the columns.