BigQuery: DQD query 'field_plausible_after_birth.sql)' not translated correctly

[pjlammertyn]

DQD query field_plausible_after_birth.sql.

translated to to:

/*********
PLAUSIBLE_AFTER_BIRTH
Checks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)
Birthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).
Denominator is number of events with a non-null date.
Parameters used in this template:
cdmDatabaseSchema = azd-dev-omop-pjl.omop
cdmTableName = CONDITION_ERA
cdmFieldName = condition_era_end_date
**********/
select 
    num_violated_rows, 
    case 
        when denominator.num_rows = 0 then 0 
        else 1.0*num_violated_rows/denominator.num_rows 
    end as pct_violated_rows, 
    denominator.num_rows as num_denominator_rows
from
(
    select 
        COUNT(violated_rows.violating_field) as num_violated_rows
    from
    (
        /*violatedRowsBegin*/
        select 
            'CONDITION_ERA.condition_era_end_date' as violating_field, 
            cdmtable.*
        from azd-dev-omop-pjl.omop.condition_era cdmtable
        join azd-dev-omop-pjl.omop.person p 
            on cdmtable.person_id = p.person_id
        where cdmtable.condition_era_end_date is not null and 
            IF(SAFE_CAST(cdmtable.condition_era_end_date  AS DATE) IS NULL,PARSE_DATE('%Y%m%d', cast(cdmtable.condition_era_end_date  AS STRING)),SAFE_CAST(cdmtable.condition_era_end_date  AS DATE)) < coalesce(
                p.birth_datetime, 
                cast(concat(
                    p.year_of_birth,
                    coalesce(
                        SUBSTR(CONCAT('0', cast(p.month_of_birth as STRING), 2),-'01'
                    ),
                    coalesce(
                        SUBSTR(CONCAT('0', cast(p.day_of_birth as STRING), 2),-'01'
                    )
                ) as date)
            )
        /*violatedRowsEnd*/
    ) violated_rows
) violated_row_count,
(
    select 
        COUNT(*) as num_rows
    from azd-dev-omop-pjl.omop.condition_era cdmtable
    where cdmtable.condition_era_end_date is not null
))) denominator
;

[schuemie]

This should now be fixed in the develop branch: https://github.com/OHDSI/SqlRender/commit/0ff607b6cf50ccf5ece05241d18bd91f9a7ed253

Could you give this a try? You can install using

remotes::install_github("ohdsi/SqlRender", ref = "develop")

[pjlammertyn]

tested with v1.18.1 and works