search

OHDSI / Achilles

Automated Characterization of Health Information at Large-scale Longitudinal Evidence Systems (ACHILLES) - descriptive statistics about a OMOP CDM database
https://ohdsi.github.io/Achilles/
130 stars 121 forks source link

Empty procedure_ocurrence table causes problems #586

Closed yampeku closed 3 years ago

yampeku commented 3 years ago

I'm getting an _ERROR: relation "results.achillesresults" does not exist due to Achilles having two missing tables (s_tmpach_631 and s_tmpach_632, error comes from first one but the other one is also missing). These are the PROCEDURE_OCCURRENCE tables, which both are not possible to be created due to an error

DBMS: postgresql

Error: org.postgresql.util.PSQLException: ERROR: division by zero

SQL: CREATE TEMP TABLE s_tmpach_631

AS WITH op_outside AS ( SELECT COUNT(DISTINCT po.person_id) AS person_count FROM omop_fiibap_cdm.procedure_occurrence po LEFT JOIN omop_fiibap_cdm.observation_period op ON po.person_id = op.person_id AND po.procedure_date >= op.observation_period_start_date AND po.procedure_date <= op.observation_period_end_date WHERE op.person_id IS NULL ), po_total AS ( SELECT COUNT(DISTINCT person_id) person_count FROM omop_fiibap_cdm.procedure_occurrence ) SELECT 631 AS analysis_id, CAST(CAST(1.0*op.person_count/po.person_count AS NUMERIC(7,6)) AS VARCHAR(255)) AS stratum_1, CAST(op.person_count AS VARCHAR(255)) AS stratum_2, CAST(po.person_count AS VARCHAR(255)) AS stratum_3, CAST(NULL AS VARCHAR(255)) AS stratum_4, CAST(NULL AS VARCHAR(255)) AS stratum_5, SIGN(op.person_count) AS count_value

FROM op_outside op CROSS JOIN po_total po

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

Platform: x86_64-w64-mingw32

Attached base packages:

Other attached packages:

table s_tmpach_632 throws the same error. The thing is, it's normal they have such an error, as table is empty (i.e. we have no procedure_occurrence yet). Any ideas on how to deal with this use case?

ericaVoss commented 3 years ago

@fdefalco or @AnthonyMolinaro - would they just not run these since they get a division by 0 error? There is a way to ignore some analysis right?

AnthonyMolinaro commented 3 years ago

@ericaVoss Hi Erica, yes, exactly! We addressed this issue recently, here.

@yampeku Please review the approach in the linked issue and let us know if that works for you.

yampeku commented 3 years ago

It worked :)