CODEWITHSUNDEEP
sqlgoogle-bigquerydynamic-sql
kim
kim

Reputation: 567

How to fix variable declaration error from dynamic sql in bigquery?

I am trying to get sum of column values for more than 50 columns, so I used dynamic sql where my tables in bigquery. I looked into dynamic sql syntax and came up this solution. My goal is to get sum of columns values in 6 months where I have a lot of columns to do sum.

my attempt in dynamic sql

here is my dynamic sql that I tried in bigquery:

DECLARE column_list ARRAY<STRING>;
DECLARE query STRING;

SET column_list = [  'prod_ab_amt' ,
  'prod_bf_amt' ,
  'prod_gm_amt' ,
  'catg_cg_amt' ,
  'catg_dh_amt' ,
  'catg_xy_amt' ,
  'catg_mk_amt' ,
  'brand_hk_amt' ,
  'brand_hp_amt' ,
  'brand_vm_amt']

SET query = (
  SELECT CONCAT(
    'SELECT user_id, extract(year from purchase_date) as purch_yr, ',
     ' CASE WHEN EXTRACT(MONTH FROM purchase_date) < 7 THEN 1 ELSE 2 END AS purch_half_period, ',
    STRING_AGG('SUM(', column, ') AS ', column),
    ' FROM trans',
    ' GROUP BY user_id, purch_yr, purch_half_period'
  )
  FROM UNNEST(column_list) AS column
);

EXECUTE IMMEDIATE query;

but I am getting this error though:

variable declarations are allowed only at the start of a block or script at [13:1]

update- new error

based on comment, I put declare variables in the begging, but now I am getting this error:

Query error: No matching signature for aggregate function STRING_AGG for argument types: STRING, STRING, STRING, STRING. Supported signatures: STRING_AGG(STRING); STRING_AGG(STRING, STRING); STRING_AGG(BYTES); STRING_AGG(BYTES, BYTES)

does anyone know is this really syntax error in this dynamic sql? any thoughts?

update

input table and values are simulated. columns of input table also minimally simulated for reproducible purpose. I think using dynamic sql is right way to do but now end up with error. Can anyone point me out how to make this dynamic sql work?

minimal data

here is my minimal data that simulated to actual input table: dbfiddle.

can anyone suggest how to fix the issues in above dynamic sql? I intend to make this work in bigquery standard sql. any helps would be appreciated. thanks

Upvotes: 0

Views: 1077

Answers (1)

nbk
nbk

Reputation: 49403

the aggregation function needs a single string so CONCAT the strings in the STRING_AGG

 STRING_AGG(CONCAT('SUM(', column, ') AS ', column)),

Upvotes: 1

Related Questions