some column names in the visit detail table on the sql server test database are incorrect

[ablack3]

Hi @leeevans,

Will you make sure the column names in the CDM test databases are correct? Are you using the standard DDL from the CommonDataModel package to create these CDMs?

library(dplyr)

con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = Sys.getenv("SQL_SERVER_DRIVER"),
                      Server   = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
                      Database = Sys.getenv("CDM5_SQL_SERVER_CDM_DATABASE"),
                      UID      = Sys.getenv("CDM5_SQL_SERVER_USER"),
                      PWD      = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
                      TrustServerCertificate="yes",
                      Port     = 1433)

(actual <- names(DBI::dbGetQuery(con, "select top 1 * from CDMV5.dbo.visit_detail")))
#>  [1] "visit_detail_id"             "person_id"                  
#>  [3] "visit_detail_concept_id"     "visit_detail_start_date"    
#>  [5] "visit_start_datetime"        "visit_detail_end_date"      
#>  [7] "visit_end_datetime"          "visit_type_concept_id"      
#>  [9] "provider_id"                 "care_site_id"               
#> [11] "admitting_source_concept_id" "discharge_to_concept_id"    
#> [13] "preceding_visit_detail_id"   "visit_source_value"         
#> [15] "visit_source_concept_id"     "admitting_source_value"     
#> [17] "discharge_to_source_value"   "visit_detail_parent_id"     
#> [19] "visit_occurrence_id"

DBI::dbDisconnect(con)


expected <- CDMConnector:::spec_cdm_field[["5.3"]] %>% 
  filter(cdmTableName == "visit_detail") %>% 
  pull(cdmFieldName)



setdiff(actual, expected)
#> [1] "visit_start_datetime"    "visit_end_datetime"     
#> [3] "visit_type_concept_id"   "visit_source_value"     
#> [5] "visit_source_concept_id"

^{Created on 2024-01-26 with reprex v2.0.2}

[ablack3]

@leeevans will you check that the test databases have correct column names?
Why not use the standard DDL (in the CommonDataModel package) when creating these databases?

The note table and visit_detail table seem to have incorrect column names on sql server. I'm using the v5.3 CDM. Will you check these?