Error ORA-00904: "#Source Codes": invalid identifier

[apopassias]

Running cdmInspection() we get an error as follows:

results<-cdmInspection( connectionDetails = connectionDetails, cdmDatabaseSchema = "x", resultsDatabaseSchema = "x", vocabDatabaseSchema = "x", oracleTempSchema = "x", databaseId, databaseName="", databaseDescription = databaseDescription, runVocabularyChecks = TRUE, runDataTablesChecks = TRUE, runPerformanceChecks = TRUE, runWebAPIChecks = TRUE, smallCellCount = smallCellCount, baseUrl = baseUrl, sqlOnly = FALSE, outputFolder = "C:/CDM_OUTPUT", verboseMode = TRUE ) Connecting using Oracle driver using THIN to connect Connecting using Oracle driver using THIN to connect CDM Inspection of database started (cdm_version=5.4) Running Data Table Checks Connecting using Oracle driver using THIN to connect Data tables count query executed successfully in 1.74 secs Connecting using Oracle driver using THIN to connect Total number of records over time query executed successfully in 0.29 secs Connecting using Oracle driver using THIN to connect Number of records per person query executed successfully in 0.15 secs Connecting using Oracle driver using THIN to connect Number of records per person query executed successfully in 0.18 secs Connecting using Oracle driver using THIN to connect Vocabulary table successfully extracted Running Vocabulary Checks Connecting using Oracle driver using THIN to connect Failed see C:/CDM_OUTPUT/mapping_levels_drugsErr.txt for more details Failed see C:/CDM_OUTPUT/mapping_levels_drugsErr.txt for more details Error in lapply(list(...), function(x) { : object 'smallCellCount' not found

As we can see in the file mapping_levels_drugsErr.txt the error is:

DBMS: oracle

Error: java.sql.SQLSyntaxErrorException: ORA-00904: "#Source Codes": invalid identifier

SQL: -- Levels at which drugs are mapped select concept_class_id as class, COUNT(drug_exposure_id) as n_records, COUNT(distinct person_id) as n_patients, COUNT(distinct drug_source_value) as n_source_codes from CDNEHDEN.drug_exposure join CDNEHDEN.concept on drug_concept_id=concept_id where concept.domain_id = 'Drug' group by concept_class_id order by "#Source Codes" DESC

The auto-generated script probably contains a syntax error, as it refers to the unknown "#Source Codes" parameter If we remove that and run the corrected script on SQL developer then we get the expected result.

Any suggestion?

[MaximMoinat]

Thanks for reporting, this has been fixed in release 1.2.2

[apopassias]

Thank you

another error has occurred:

generateResultsDocument(+ results,+ outputFolder,+ authors=authors,+ databaseId = databaseId,+ databaseName = databaseName,+ databaseDescription = databaseDescription,+ smallCellCount = smallCellCount+ )Error in arrange():i In argument: ..1 = DOMAIN.Caused by error:! object 'DOMAIN' not foundRun rlang::last_trace() to see where the error occurred.> rlang::last_trace()<error/dplyr:::mutate_error>Error in arrange():i In argument: ..1 = DOMAIN.Caused by error:! object 'DOMAIN' not found ---Backtrace: x 1. +-CdmInspection::generateResultsDocument(...)

2. | -... %>% ... 3. +-dplyr::rename(...) 4. +-dplyr::mutate(...)
3. +-dplyr::arrange(., DOMAIN) 6. -dplyr:::arrange.data.frame(., DOMAIN) 7. -dplyr:::arrange_rows(.data, dots = dots, locale = .locale) 8. +-dplyr::mutate(data, :=("{name}", !!dot), .keep = "none") 9. -dplyr:::mutate.data.frame(data, :=("{name}", !!dot), .keep = "none") 10. -dplyr:::mutate_cols(.data, dplyr_quosures(...), by) 11. +-base::withCallingHandlers(...)
4. -dplyr:::mutate_col(dots[[i]], data, mask, new_columns)
5. -mask$eval_all_mutate(quo) 14. -dplyr (local) eval()Run rlang::last_trace(drop = FALSE) to see 3 hidden frames.> rlang::last_trace(drop = FALSE)<error/dplyr:::mutate_error>Error in arrange():i In argument: ..1 = DOMAIN.Caused by error:! object 'DOMAIN' not found ---Backtrace: x 1. +-CdmInspection::generateResultsDocument(...)
6. | -... %>% ... 3. +-dplyr::rename(...) 4. +-dplyr::mutate(...)
7. +-dplyr::arrange(., DOMAIN) 6. +-dplyr:::arrange.data.frame(., DOMAIN) 7. | -dplyr:::arrange_rows(.data, dots = dots, locale = .locale) 8. | +-dplyr::mutate(data, :=("{name}", !!dot), .keep = "none") 9. | -dplyr:::mutate.data.frame(data, :=("{name}", !!dot), .keep = "none") 10. | -dplyr:::mutate_cols(.data, dplyr_quosures(...), by) 11. | +-base::withCallingHandlers(...)
8. | -dplyr:::mutate_col(dots[[i]], data, mask, new_columns)
9. | -mask$eval_all_mutate(quo) 14. | -dplyr (local) eval() 15. -base::.handleSimpleError(...) 16. -dplyr (local) h(simpleError(msg, call)) 17. -rlang::abort(message, class = error_class, parent = parent, call = error_call)

Any ideas?

Apostolis Pasias

PS: Sorry for the direct message, but I'm already behind schedule due to the previous errors:

https://github.com/EHDEN/CdmInspection/issues/98

https://github.com/EHDEN/CdmInspection/issues/99

On Mon, Nov 13, 2023 at 10:22 AM Maxim Moinat @.***> wrote:

Thanks for reporting, this has been fixed in release 1.2.2

— Reply to this email directly, view it on GitHub https://github.com/EHDEN/CdmInspection/issues/99#issuecomment-1807659776, or unsubscribe https://github.com/notifications/unsubscribe-auth/BC2PLM7L7JT2ZLQK6Z4Z5MLYEHKENAVCNFSM6AAAAAA7H2TPQ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMBXGY2TSNZXGY . You are receiving this because you authored the thread.Message ID: @.***>

[MaximMoinat]

@apopassias Sorry for all these bugs you are encountering. And many thanks for reporting. I have done some additional tests and releases another patch version: 1.2.3. Hope this works. If not, please revert to v1.1 with: remotes::install_github('EHDEN/CdmInspection', ref='v1.1.0')

[apopassias]

It works.

Thanks

On Mon, Nov 13, 2023 at 4:05 PM Maxim Moinat @.***> wrote:

@apopassias https://github.com/apopassias Sorry for creating all these issues. I have done some additional tests and releases another patch version: 1.2.3. Hope this works. If not, please revert to v1.1 with: remotes::install_github('EHDEN/CdmInspection', ref='v1.1.0')

— Reply to this email directly, view it on GitHub https://github.com/EHDEN/CdmInspection/issues/99#issuecomment-1808227604, or unsubscribe https://github.com/notifications/unsubscribe-auth/BC2PLMY5I5IHHHLTCZA6Y4DYEISLLAVCNFSM6AAAAAA7H2TPQ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMBYGIZDONRQGQ . You are receiving this because you were mentioned.Message ID: @.***>