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 |

Answer 1

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