Questions about success of a Strategus Study Template run

[bill-baumgartner]

Hello @anthonysena and OHDSI Evidence Network team,

I have run the Strategus Study Template on one of our local OMOP instances to validate our readiness to participate in the OHDSI Evidence Network. According to the final summary, it looks like the run succeeded (all green check marks -- see screenshot below), however during the run an SQL error was logged (shown below) and there are a number of warnings that were output (also in screenshot below). So, my question is, did the run truly succeed or do you think there are steps needed to fix the failed query and address the reported warnings? Let me know if I can provide further details. Thanks for any info you can provide!

Environment:

• R v4.4.1
• OMOP CDM v5.4
• DBMS: BigQuery
• Strategus: current GitHub HEAD

SQL Error:

DBMS:
bigquery

Error:
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: SELECT list expression references column target_cohort_definition_id which is neither grouped nor aggregated at [19:9]

SQL:
CREATE TABLE strategus_test_db.k0dzbxqctwo_tte_summary
 AS
SELECT
'311816993' as database_id, temp.*
FROM
(
--daily counting for +/- 100 days, jenna comment why 100
 select target_cohort_definition_id,
outcome_cohort_definition_id,
outcome_type,
target_outcome_type,
time_to_event,
num_events,
'per 1-day' as time_scale
 from strategus_test_db.k0dzbxqctwo_tte
where abs(time_to_event) <= 100
union all
--30-day counting for +/- 1080 days (~ 3 years)
 select target_cohort_definition_id,
outcome_cohort_definition_id,
outcome_type,
target_outcome_type,
case when time_to_event = 0 then 0
     when time_to_event < 0 then (floor(time_to_event/30)-1)*30
     when time_to_event > 0 then (floor(time_to_event/30)+1)*30
     end as time_to_event,
sum(num_events) as num_events,
'per 30-day' as time_scale
 from strategus_test_db.k0dzbxqctwo_tte
where abs(time_to_event) <= 1080
  group by  5, 2, 3, 4, case when time_to_event = 0 then 0
       when time_to_event < 0 then (floor(time_to_event/30)-1)*30
       when time_to_event > 0 then (floor(time_to_event/30)+1)*30 end
union all
--365-day counting for +/- all days
 select target_cohort_definition_id, 2, 3, 4, case when time_to_event = 0 then 0
       when time_to_event < 0 then (floor(time_to_event/365)-1)*365
       when time_to_event > 0 then (floor(time_to_event/365)+1)*365 end as time_to_event, sum(num_events) as num_events, 'per 365-day' as time_scale
 from strategus_test_db.k0dzbxqctwo_tte
 group by  1, 2, 3, 4, 5 ) temp


R version:
R version 4.4.1 (2024-06-14)

Platform:
x86_64-pc-linux-gnu

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

Other attached packages:
- DatabaseConnector (6.4.0)
- reticulate (1.39.0)