I'm getting the following notice

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

in BigQuery

I need the products array to show as nested under the accounts row

SELECT *,
    ARRAY(
    SELECT
      AS STRUCT 
      Account AS Licensed_Account, 
      Country AS Licensed_Country, 
      Exhibit_X_or_Y_Customer AS Agreement, 
      Dedicated_or_Shared AS Usage_Type, 
      Kyndryl_Account_ID, 
      Item_Number AS SKU, 
      License_Key_vSphere_8, 
      Downgraded_vSphere_7_Keys, 
      Downgraded_vSphere_6_Keys, 
      CAST(Site_ID AS STRING) AS Site_ID, 
      CAST(Contract_Number AS STRING) AS Contract_Number, 
      ARRAY(
      SELECT
        AS STRUCT *
      FROM
        `VCSP_Reporting.CPM Detail Data` AS cpmdetail
      WHERE
        cpmdetail.Account = products.Account
        AND cpmdetail.License_Key = products.License_Key_vSphere_8
        OR cpmdetail.License_Key = products.Downgraded_vSphere_7_Keys
        OR cpmdetail.License_Key = products.Downgraded_vSphere_6_Keys
        OR SKU = 'VCF-CLD-FND-VSP-5' ) AS CPM_Detail_Data
    FROM
      `VCSP_Reporting.License Tracker` AS products
      WHERE products.Account = Accounts.Account)
  FROM
    `VCSP_Reporting.Accounts` AS accounts
    WHERE Account IS NOT NULL ORDER BY Account ASC

I'm getting the following notice

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

in BigQuery

I need the products array to show as nested under the accounts row

SELECT *,
    ARRAY(
    SELECT
      AS STRUCT 
      Account AS Licensed_Account, 
      Country AS Licensed_Country, 
      Exhibit_X_or_Y_Customer AS Agreement, 
      Dedicated_or_Shared AS Usage_Type, 
      Kyndryl_Account_ID, 
      Item_Number AS SKU, 
      License_Key_vSphere_8, 
      Downgraded_vSphere_7_Keys, 
      Downgraded_vSphere_6_Keys, 
      CAST(Site_ID AS STRING) AS Site_ID, 
      CAST(Contract_Number AS STRING) AS Contract_Number, 
      ARRAY(
      SELECT
        AS STRUCT *
      FROM
        `VCSP_Reporting.CPM Detail Data` AS cpmdetail
      WHERE
        cpmdetail.Account = products.Account
        AND cpmdetail.License_Key = products.License_Key_vSphere_8
        OR cpmdetail.License_Key = products.Downgraded_vSphere_7_Keys
        OR cpmdetail.License_Key = products.Downgraded_vSphere_6_Keys
        OR SKU = 'VCF-CLD-FND-VSP-5' ) AS CPM_Detail_Data
    FROM
      `VCSP_Reporting.License Tracker` AS products
      WHERE products.Account = Accounts.Account)
  FROM
    `VCSP_Reporting.Accounts` AS accounts
    WHERE Account IS NOT NULL ORDER BY Account ASC

• google-bigquery

1 Answer 1

This query needs to be rewritten with left join and CTE. The idea is the following: Build for each table a query under the WITHstatement. In the next table build an array of struct object. Also keep the joining columns and group by them. Next combine everything by left joins. Here an example. Since no minimal working example was provided, this query is not tested:

WITH CTE_CPM_Detail_Data AS (
  SELECT
*
  FROM
    `VCSP_Reporting.CPM Detail Data`

),

CTE_License_Tracker AS (
  SELECT 
   *, 
#TODO: list of all needed columns, array_agg(struct(col1 as col1, col2 as col2  )
  FROM 
    `VCSP_Reporting.License Tracker`
#left join CTE_CPM_Detail_Data on .....

),

tbl1 as (
Select Account, array_agg( struct(Account AS Licensed_Account, 
      Country AS Licensed_Country, 
      Exhibit_X_or_Y_Customer AS Agreement, 
      Dedicated_or_Shared AS Usage_Type, 
      Kyndryl_Account_ID, 
      Item_Number AS SKU, 
      License_Key_vSphere_8, 
      Downgraded_vSphere_7_Keys, 
      Downgraded_vSphere_6_Keys, 
      CAST(Site_ID AS STRING) AS Site_ID, 
      CAST(Contract_Number AS STRING) AS Contract_Number ) )
from CTE_License_Tracker
where Account IS NOT NULL
group by 1
)


SELECT 
  a.*,
  b
FROM 
  `VCSP_Reporting.Accounts` a
left join tbl1 using(Account 
WHERE 
  a.Account IS NOT NULL
ORDER BY 
  a.Account ASC;