Bad SQL generated by Achilles (?) for "Begin Primary Events"

Implementers
1

Dear OMOP community,

We have the problem that in one of our OMOP instances no statistics about visits are generated (in Atlas under “Data Sources”). Likewise, some other data categories are missing the record count (RC).

After a bit of research, we saw in the logs that an SQL was failing, which could be related to the error. The problem is that generated code is missing in a subquery: FROM ( ) e. We tried to find the program code that generates this SQL (“Begin Primary Events”), but had no success.

            -- Begin Primary Events
            SELECT p.ordinal AS event_id,
                   p.person_id,
                   p.start_date,
                   p.end_date,
                   op_start_date,
                   op_end_date,
                   cast(p.visit_occurrence_id AS bigint) AS visit_occurrence_id
            FROM   (
                            SELECT   e.person_id,
                                     e.start_date,
                                     e.end_date,
                                     row_number() OVER (partition BY e.person_id ORDER BY e.sort_date ASC)    ordinal,
                                     op.observation_period_start_date                                      AS op_start_date,
                                     op.observation_period_end_date                                        AS op_end_date,
                                     cast(e.visit_occurrence_id AS bigint)                                 AS visit_occurrence_id
                            FROM     (  ) e
                            JOIN     cds_cdm.observation_period op
                            ON       e.person_id = op.person_id
                            AND      e.start_date >= op.observation_period_start_date
                            AND      e.start_date <= op.observation_period_end_date

The CDM is in version 5.3.1.

Any tips on how we could address the problem would be greatly appreciated.

Kind regards from Erlangen, Germany,
Sebastian

2

That looks like an ‘inclusion rule’ type of query, where it attempts to establish the primary events by querying for a person_id, start_date, end_date, etc from a source table. The source table would be specified in the FROM() clause, but it looks blank.

I’m not sure where in Achilles we would need such an operation. Are you sure this is coming from an Achilles query?

3

Did you check table access permissions? This happened to me in this case.

4

Hello!

I would begin by checking the observation_period table to ensure that its been generated correctly for the clinical events in your OMOP. Without a valid observation period, you will not see statistics appear when performing analysis in ATLAS.

Dave Barman
Odysseus Data Services

5

The problem is definitely the empty FROM () clause in the sql, it’s just that I can’t tell where in the Achilles scripts that is being generated from. It’s been a while since I looked at the achilles script, but I don’t recall any use of cohort queries (which this one looks like).

It could be problems that the others have suggested (obseration_period isn’t populated, or table permissions) but the sql you’re showing sounds like a problem with a cohort query…

6

Looks like this template in circe-be:

In case criteriaQueries here is an empty ArrayList, would it result in an empty FROM( ) clause?

7

Definitely, but my question is where this is being used in Achilles?

If you try to build a cohort query with an empty ‘primary events’ list, you will get an empty string from the @criteriaQueries replacement.

That would explain the query that you see, but it doesn’t explain the relationship to Achilles.