Support UNION ALL function in BigQuery SQL Issue

Question

How can i make this query run on GCP big query without error:

'Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.'

Do you guys have any ideia how can i resolve this conflict?

 Select
  x.subject_id as PatientId
  ,sum(x.BPS) as sbp
  ,sum(x.RR) as rr
  ,sum(x.sepsis) as sepsis
From (
  Select
    c.subject_id
    ,(
    Select  (Case
      WHEN cast(c2.value as numeric) <= 90 THEN 3
            WHEN cast(c2.value as numeric) >= 91 AND cast(c2.value as numeric) <= 100 THEN 2
            WHEN cast(c2.value as numeric) >= 101 AND cast(c2.value as numeric) < 110 THEN 1
            WHEN cast(c2.value as numeric) >= 111 AND cast(c2.value as numeric) < 219 THEN 0
      WHEN cast(c2.value as numeric) >= 220 THEN 3
      else 0
         End)
    From    chartevents c2
    Where   c2.subject_id = c.subject_id
        and c2.itemid = c.itemid
        limit 1
    ) as BPS
    ,0 as RR
  ,0 as sepsis
  From
    chartevents c
  Where
    c.itemid = 220050
  Group by
    c.subject_id
    ,c.itemid
  Union all
  Select
    c.subject_id
    ,0 as BPS
    ,(Select  (Case
      WHEN cast(c2.value as numeric) <= 8 THEN 3
            WHEN cast(c2.value as numeric) >= 9 AND cast(c2.value as numeric) <= 11 THEN 1
            WHEN cast(c2.value as numeric) >= 12 AND cast(c2.value as numeric) <= 20 THEN 0
            WHEN cast(c2.value as numeric) >= 21 AND cast(c2.value as numeric) <= 24 THEN 2
            WHEN cast(c2.value as numeric) >= 25 THEN 3
      else 0
         End)
    From    chartevents c2
    Where   c2.subject_id = c.subject_id
        and c2.itemid = c.itemid
       limit 1
   ) as RR
  ,0 as sepsis
  From
    chartevents c
  Where
    c.itemid = 220210
  Group by
    c.subject_id
    ,c.itemid
  Union all
    Select
        c.subject_id
        ,0 as BPS
        ,0 as RR
      ,(case when exists (
                      Select  1
                      From    diagnoses_icd d
                      Where   d.subject_id = c.subject_id
                              and d.icd9_code like '%99591%'
                      )
              then 1
              else 0
        end) as sepsis
      From
        chartevents c
      Where
        c.itemid = 228334
      Group by
        c.subject_id
        ,c.itemid
) x
Group by
   x.subject_id
   limit 5
;

Desired output:

| PatientId | sbp | rr | sepsis|

| 1 | 0 | 0 | 0 |
| 2 | 2 | 3 | 1 |

Sample data and desired results would make your question easier to follow. — GMB
In addition to what GMB says, I would also strongly suggest simplifying the query. — Gordon Linoff
I Added more information. How can I simplify this query and bring the same results. @GordonLinoff — Vinicius Ferreira
@ViniciusFerreira . . . For instance, you probably don't need all the columns to generate the error. The error is probably generated in just one of the subqueries of the union all. There are two ideas. — Gordon Linoff

Sergey Geron Sergey Geron · Accepted Answer · 2020-11-23T04:57:26

This is probably happening because of chartevents c2 subqueries. Try to convert them to ARRAY_AGG construction.

This will generate error:

SELECT
  c.subject_id,
  (
      SELECT c2.value * 2
      FROM chartevents c2
      WHERE c2.subject_id = c.subject_id
        AND c2.itemid = c.itemid
      LIMIT 1
  ) as BPS
FROM
  chartevents c

This will work:

SELECT
  c.subject_id,
  ARRAY_AGG(c2.value * 2 LIMIT 1)[OFFSET(0)] AS BPS
FROM chartevents AS c
JOIN chartevents AS c2
ON c2.subject_id = c.subject_id
  AND c2.itemid = c.itemid

Support UNION ALL function in BigQuery SQL Issue

How can i make this query run on GCP big query without error:

1 Answers