2019-07-31 10:52:35.208 ERROR http-nio-8080-exec-8 org.ohdsi.webapi.util.GenericExceptionMapper -  - org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select hr1.cohort_definition_id,
       'First' as record_type,
       c1.concept_id,
       c1.concept_name,
       hr1.duration,
       hr1.count_value,
       case
         when t1.count_value > 0 then 1.0 * hr1.count_value / t1.count_value
         else 0
       end     as pct_persons
from   (select cohort_definition_id,
               cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64)      as concept_id,
               cast(case when CASE WHEN SAFE_CAST(stratum_2 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_2 else null end  as int64) * 30 as duration,
               count_value
        from   synpuf_110k_results.heracles_results
        where  analysis_id in ( 1870 )
               and cohort_definition_id = ?) hr1
       inner join (select cohort_definition_id,
                          -1 * cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) * 30                as
              duration,
                          sum(count_value)
                            over (
                              partition by cohort_definition_id
                              order by -1* cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) * 30 asc) as
              count_value
                   from   synpuf_110k_results.heracles_results
                   where  analysis_id in ( 1805 )
                          and cohort_definition_id = ?
                          and cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) > 0
                   union distinct select hr1.cohort_definition_id,
                          cast(case when CASE WHEN SAFE_CAST(hr1.stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then hr1.stratum_1 else null end  as int64) * 30
                          as
                          duration,
                          t1.count_value - sum(hr1.count_value)
                          over (
                            partition by hr1.cohort_definition_id
                            order by cast(case when CASE WHEN SAFE_CAST(hr1.stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then hr1.stratum_1 else null end  as int64) * 
                          30 asc) as
                          count_value
                   from   synpuf_110k_results.heracles_results hr1
                          inner join ( select cohort_definition_id,
                                             sum(count_value) as count_value
                                       from synpuf_110k_results.heracles_results
                                      where analysis_id = 1806
                                             and cohort_definition_id = ?
                                       group by  1 ) t1
                                  on hr1.cohort_definition_id =
                                     t1.cohort_definition_id
                   where  hr1.analysis_id in ( 1806 )
                          and hr1.cohort_definition_id in
                              ( ? )) t1
               on hr1.cohort_definition_id = t1.cohort_definition_id
                  and hr1.duration = t1.duration
       inner join (  select cohort_definition_id,
                          cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) as concept_id,
                          sum(count_value)           as count_value
                     from synpuf_110k_results.heracles_results
                   where  analysis_id in ( 1870 )
                          and cohort_definition_id = ?
                     group by  2, 2 having sum(heracles_results.count_value) > ? ) ct1
               on hr1.cohort_definition_id = ct1.cohort_definition_id
                  and hr1.concept_id = ct1.concept_id
       inner join synpuf_110k.concept c1
               on hr1.concept_id = c1.concept_id
where  t1.count_value > ?
        and c1.concept_id = ?
union distinct select hr1.cohort_definition_id,
       'All' as record_type,
       c1.concept_id,
       c1.concept_name,
       hr1.duration,
       hr1.count_value,
       case
         when t1.count_value > 0 then 1.0 * hr1.count_value / t1.count_value
         else 0
       end   as pct_persons
from   (select cohort_definition_id,
               cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64)      as concept_id,
               cast(case when CASE WHEN SAFE_CAST(stratum_2 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_2 else null end  as int64) * 30 as duration,
               count_value
        from   synpuf_110k_results.heracles_results
        where  analysis_id in ( 1871 )
               and cohort_definition_id = ?) hr1
       inner join (select cohort_definition_id,
                          -1 * cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) * 30                as
              duration,
                          sum(count_value)
                            over (
                              partition by cohort_definition_id
                              order by -1* cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) * 30 asc) as
              count_value
                   from   synpuf_110k_results.heracles_results
                   where  analysis_id in ( 1805 )
                          and cohort_definition_id = ?
                          and cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) > 0
                   union distinct select hr1.cohort_definition_id,
                         cast(case when CASE WHEN SAFE_CAST(hr1.stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then hr1.stratum_1 else null end  as int64) * 30
                          as
                          duration,
                          t1.count_value - sum(hr1.count_value)
                          over (
                            partition by hr1.cohort_definition_id
                            order by cast(case when CASE WHEN SAFE_CAST(hr1.stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then hr1.stratum_1 else null end  as int64) * 
                          30 asc) as
                          count_value
                   from   synpuf_110k_results.heracles_results hr1
                          inner join ( select cohort_definition_id,
                                             sum(count_value) as count_value
                                       from synpuf_110k_results.heracles_results
                                      where  analysis_id = 1806
                                             and cohort_definition_id = ?
                                       group by  1 ) t1
                                  on hr1.cohort_definition_id =
                                     t1.cohort_definition_id
                   where  hr1.analysis_id in ( 1806 )
                          and hr1.cohort_definition_id in
                              ( ? )) t1
               on hr1.cohort_definition_id = t1.cohort_definition_id
                  and hr1.duration = t1.duration
       inner join (  select cohort_definition_id,
                          cast(case when CASE WHEN SAFE_CAST(stratum_1 AS FLOAT64) IS NULL THEN 0 ELSE 1 END = 1 then stratum_1 else null end  as int64) as concept_id,
                          sum(count_value)           as count_value
                     from synpuf_110k_results.heracles_results
                   where  analysis_id in ( 1871 )
                          and cohort_definition_id = ?
                     group by  2, 2 having sum(heracles_results.count_value) > ? ) ct1
               on hr1.cohort_definition_id = ct1.cohort_definition_id
                  and hr1.concept_id = ct1.concept_id
       inner join synpuf_110k.concept c1
               on hr1.concept_id = c1.concept_id
where  t1.count_value > ?
        and c1.concept_id = ?]; SQL state [HY000]; error code [100032]; [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "q",
    "locationType" : "parameter",
    "message" : "SELECT list expression references column cohort_definition_id which is neither grouped nor aggregated at [54:29]",
    "reason" : "invalidQuery"
  } ],
  "message" : "SELECT list expression references column cohort_definition_id which is neither grouped nor aggregated at [54:29]",
  "status" : "INVALID_ARGUMENT"
}; nested exception is java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "q",
    "locationType" : "parameter",
    "message" : "SELECT list expression references column cohort_definition_id which is neither grouped nor aggregated at [54:29]",
    "reason" : "invalidQuery"
  } ],
  "message" : "SELECT list expression references column cohort_definition_id which is neither grouped nor aggregated at [54:29]",
  "status" : "INVALID_ARGUMENT"