Hi,
I have been doing some testing trying to run Achilles against a BigQuery database. A number of tests have been failing with something along these lines as below. What’s likely to cause this? Is this expected?
DBMS:
bigquery
Error:
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request
{
"code" : 400,
"errors" : [ {
"domain" : "global",
"location" : "q",
"locationType" : "parameter",
"message" : "Parameterized types are not allowed in CAST expressions.",
"reason" : "invalidQuery"
} ],
"message" : "Parameterized types are not allowed in CAST expressions.",
"status" : "INVALID_ARGUMENT"
}
SQL:
CREATE TABLE omop.tmpach_231
AS WITH op_outside as (
select
COUNT(distinct vo.person_id) as person_count
from
omop.visit_occurrence vo
left join
omop.observation_period op
on
vo.person_id = op.person_id
and
vo.visit_start_date >= op.observation_period_start_date
and
vo.visit_start_date <= op.observation_period_end_date
where
op.person_id is null
), vo_total as (
select
COUNT(distinct person_id) person_count
from
omop.visit_occurrence
)
SELECT 231 as analysis_id,
case when vo.person_count != 0 then
cast(cast(1.0*op.person_count/vo.person_count as numeric(7,6)) as STRING)
else
cast(null as STRING)
end as stratum_1,
cast(op.person_count as STRING) as stratum_2,
cast(vo.person_count as STRING) as stratum_3,
cast(null as STRING) as stratum_4,
cast(null as STRING) as stratum_5,
sign(op.person_count) as count_value
FROM op_outside op
cross join
vo_total vo
R version:
R version 4.2.0 (2022-04-22)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- Achilles (1.7)
- DatabaseConnector (5.0.4)
Thanks, Matt