BigQuery: problem with spaces in column names

[pjlammertyn]

also see OHDSI/SqlRender#313

SqlRender doesn't translate the space in a column name to an underscore, In BigQuery: Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

Solution: replace the spaces with underscores

DBMS: bigquery

Error: java.sql.SQLException: [Simba]BigQueryJDBCDriver Error executing query job. Message: Syntax error: Expected ")" but got identifier "Name" at [4:35]

SQL: -- top 25 mapped conditions select from ( select row_number() over(order by COUNT(condition_occurrence_id) desc) as row_num, cr.concept_name as Concept Name, floor((COUNT(condition_occurrence_id)+99)/100)100 as jdphl2hpRecords, floor((COUNT(distinct person_id)+99)/100)*100 as jdphl2hpSubjects from omop_zidder.condition_occurrence c join omop_zidder.concept cr on c.condition_concept_id = cr.concept_id where c. condition_concept_id != 0 group by cr.concept_name having COUNT(condition_occurrence_id)>5 ) z where z.row_num <= 25 order by z.row_num

R version: R version 4.2.2 Patched (2022-11-10 r83330)

Platform: x86_64-pc-linux-gnu

Attached base packages:

stats
graphics
grDevices
utils
datasets
methods
base

Other attached packages:

CdmInspection (1.1.0)
SqlRender (1.11.0)
ParallelLogger (3.1.0)
ROhdsiWebApi (1.3.1)
DatabaseConnector (5.1.0)

[MaximMoinat]

Thanks for opening this issue, I did not realise the use of spaces in column aliases was not according to HADES guidelines. The aliases in sql are used here for easy pretty rendering in the report, to avoid having to do this in R. It seems we are not getting around that.

Have you already fixed this locally. A PR would be very welcome!