DataQualityDashboard: Incorrect quotes in CAST usage in PostgreSQL
Tried to run it on PostgreSQL database, but most of the SQL scripts end up with error like ‘ERROR: column “CAST(birth_datetime AS DATE)” does not exist’.
It seems that the produced SQL is not correct - uses quotes where it should not:
...select case when count("CAST(birth_datetime AS DATE)") = 0 ...
I tested that the same script without quotes works well:
...select case when count(CAST(birth_datetime AS DATE)) = 0 ...
Full stacktrace here:
DBMS:
postgresql
Error:
org.postgresql.util.PSQLException: ERROR: column "CAST(birth_datetime AS DATE)" does not exist
Position: 219
SQL:
/*********
FIELD LEVEL check:
CDM_FIELD - verify the field exists
Parameters used in this template:
cdmDatabaseSchema = ohdsi
cdmTableName = PERSON
cdmFieldName = CAST(birth_datetime AS DATE)
**********/
SELECT num_violated_rows, CASE WHEN denominator.num_rows = 0 THEN 0 ELSE 1.0*num_violated_rows/denominator.num_rows END AS pct_violated_rows
FROM
(
select num_violated_rows from
(
select
case when count("CAST(birth_datetime AS DATE)") = 0 then 0
else 0
end as num_violated_rows
from ohdsi.PERSON
) violated_rows
) violated_row_count,
(
SELECT 1 as num_rows
) denominator
;
R version:
R version 3.6.2 (2019-12-12)
Platform:
x86_64-apple-darwin15.6.0
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- magrittr (1.5)
- snow (0.4-3)
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Reactions: 2
- Comments: 16 (1 by maintainers)
@alondhe The
CDM_SOURCEtable was empty indeed. I inserted a row there, and now all thecdmFieldchecks are complete. The JSON file was generated. So the issue is resolved.Also I’ve noticed that this helped with the shiny page: previously both Metadata and Results sections were empty even there was the results data in the JSON file, now both sections have the expected contents.
Thank you!
So I think what we may do instead is remove the cast statements. That way we should be able to keep the quotes for all dialects as it is really the cast statements that are causing the errors.