`results` not equivalent to `results2`.
Component “rowId”: Numeric: lengths (830, 2630) differ
Component “covariateId”: Numeric: lengths (830, 2630) differ
Component “covariateValue”: Numeric: lengths (830, 2630) differ

runSpotChecks <- function(connection, cdmDatabaseSchema, ohdsiDatabaseSchema, cohortTable) {
settings <- createCovariateSettings(useDemographicsGender = TRUE,
suppressWarnings(covariateData <- getDbCovariateData(connection = connection,
suppressWarnings(covariateDataAgg <- getDbCovariateData(connection = connection,
if (covariateData$covariates %>% count() %>% pull() == 0) {
sql <- "SELECT subject_id, gender_concept_id FROM @cohortTable INNER JOIN @cdmDatabaseSchema.person ON subject_id = person_id WHERE cohort_definition_id = 1124300"
sql <- SqlRender::translate(sql, targetDialect = attr(connection, "dbms"))
results$covariateId <- results$covariateId * 1000 + 1
inner_join(covariateIds, by = "covariateId") %>%
sql <- "SELECT subject_id, YEAR(cohort_start_date) - year_of_birth AS age FROM @cohortTable INNER JOIN @cdmDatabaseSchema.person ON subject_id = person_id WHERE cohort_definition_id = 1124300"
results <- results[, c("rowId", "covariateId", "covariateValue")]
# Test analysis 102: condition occurrence long term
sql <- "SELECT DISTINCT subject_id, condition_concept_id FROM @cohortTable INNER JOIN @cdmDatabaseSchema.condition_occurrence ON subject_id = person_id WHERE cohort_definition_id = 1124300 AND condition_concept_id != 0 AND condition_start_date <= cohort_start_date AND condition_start_date >= DATEADD(DAY, -365, cohort_start_date)"
results$covariateId <- results$covariateId * 1000 + 102
results <- results[order(results$rowId, results$covariateId), ]
# Test analysis 404: drug era short term (excluding NSAIDS)
sql <- "SELECT DISTINCT subject_id, drug_concept_id FROM @cohortTable INNER JOIN @cdmDatabaseSchema.drug_era ON subject_id = person_id WHERE cohort_definition_id = 1124300 AND drug_concept_id != 0 AND drug_era_start_date <= cohort_start_date AND drug_era_end_date >= DATEADD(DAY, -30, cohort_start_date) AND drug_concept_id NOT IN (SELECT descendant_concept_id FROM @cdmDatabaseSchema.concept_ancestor WHERE ancestor_concept_id = 21603933)"
results$covariateId <- results$covariateId * 1000 + 404
# Test analysis 923: visit concept count (long term)
sql <- "SELECT subject_id, visit_concept_id, COUNT(*) AS visit_count FROM @cohortTable INNER JOIN @cdmDatabaseSchema.visit_occurrence ON subject_id = person_id WHERE cohort_definition_id = 1124300 AND visit_start_date <= cohort_start_date AND visit_start_date >= DATEADD(DAY, -365, cohort_start_date) AND visit_concept_id != 0 GROUP BY subject_id, visit_concept_id"
colnames(results) <- c("rowId", "covariateId", "covariateValue")
results$covariateId <- results$covariateId * 1000 + 923
aggCount <- aggregate(count ~ covariateId, results, sum)
aggMax <- aggregate(covariateValue ~ covariateId, results, max)
test_that("Run spot-checks at per-person level on PostgreSQL", {
pgConnection <- createUnitTestData(pgConnectionDetails, pgCdmDatabaseSchema, pgOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable)
on.exit(dropUnitTestData(pgConnection, pgOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable))
test_that("Run spot-checks at per-person level on SQL Server", {
sqlServerConnection <- createUnitTestData(sqlServerConnectionDetails, sqlServerCdmDatabaseSchema, sqlServerOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable)
on.exit(dropUnitTestData(sqlServerConnection, sqlServerOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable))
test_that("Run spot-checks at per-person level on Oracle", {
oracleConnection <- createUnitTestData(oracleConnectionDetails, oracleCdmDatabaseSchema, oracleOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable)
on.exit(dropUnitTestData(oracleConnection, oracleOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable))
test_that("Run spot-checks at per-person level on Redshift", {
redshiftConnection <- createUnitTestData(redshiftConnectionDetails, redshiftCdmDatabaseSchema, redshiftOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable)
on.exit(dropUnitTestData(redshiftConnection, redshiftOhdsiDatabaseSchema, cohortTable, cohortAttributeTable, attributeDefinitionTable))
test_that("Run spot-checks at per-person level on Eunomia", {