DYNAMIC QUERIES in GCP Big Query

by

I’m trying to execute this dynamic query in GCP bigquery.

EXECUTE IMMEDIATE 'INSERT INTO `project.dataset.tbl` (MARKET, PARAMETERS, KPI, VALUE_TYPE, `03_2023`, `04_2023`, `05_2023`, `06_2023`, `07_2023`, `08_2023`, `09_2023`, `10_2023`, `11_2023`, `12_2023`, `01_2024`, `02_2024`, `03_2024`, `04_2024`, `05_2024`, `06_2024`, `07_2024`, `08_2024`, `09_2024`, `10_2024`, `11_2024`, `12_2024`, `01_2025`, `02_2025`, `03_2025`, `04_2025`, HIST_DATE) SELECT A.MARKET, A.PARAMETERS, A.KPI, A.VALUE_TYPE, @A , @B ,cast(FORMAT_DATE(\'%Y-%m-%d\',@LD)as date) FROM ( SELECT * FROM `project.dataset.tbl` WHERE LAST_DAY(HIST_DATE) = LAST_DAY(DATE_ADD(cast(FORMAT_DATE(\'%Y-%m-%d\',@LD)as date),INTERVAL -1 MONTH)) ) AS A INNER JOIN `project.dataset.tbl2` AS B ON A.MARKET = B.MARKET AND A.PARAMETERS = B.PARAMETERS AND A.KPI = B.KPI AND A.VALUE_TYPE = B.VALUE_TYPE' USING COLUMN_A AS A,COLUMN_B AS B, LAST_DATE AS LD;

The value of column_A is A.03_2023, A.04_2023, A.05_2023, A.06_2023, A.07_2023, A.08_2023, A.09_2023, A.10_2023, A.11_2023 which is replacing @A in the dynamic query.
While the value of column_B is B.12_2023, B.01_2024, B.02_2024, B.03_2024, B.04_2024, B.05_2024, B.06_2024, B.07_2024, B.08_2024, B.09_2024, B.10_2024, B.11_2024, B.12_2024, B.01_2025, B.02_2025, B.03_2025, B.04_2025 which is replacing @B in the dynamic query.

However, the insert fails saying that the select returns only 7 rows while insert has 31 expected.
What do I do so it takes the @A is replaced with the value of COLUMN_A and @B is replaced with column_B and the total is 31 columns?

  • Does @A consists of a struct or an array of struct? Adding a .* after the A, will unstruct it. Declare A struct<y INT64,a INT64>; SET A = (Select struct(45 as y,789 as x) as a); Select A.*

    – 

  • Hi Thanks, The @A is place holder where I wanted to put the sting present in COLUMN_A variable of string data type.

    – 

Leave a Comment