0

seeking information:

I wanted to know if it is possible to create a macro variable (parametrised) in Bigquery.

Similar to SAS examples like:

%LET tempvar = ('x1','x2'.....'xn')

which I can CALL whenever needed in further SQL clauses.

Any info appreciated.

Thanks

Sid
  • 25
  • 1
  • 6
  • Possible duplicate of [Setting Big Query variables like mysql](https://stackoverflow.com/questions/29759628/setting-big-query-variables-like-mysql) – Pentium10 Jul 20 '18 at 13:29

1 Answers1

0

Depending on what exactly you want to achieve you might be able to use UDFs for that, but in general BQ does not support the parameter feature as MYSQL does.

check here

FKrauss
  • 398
  • 2
  • 9
  • thanks to FKrauss and Pentium10 for your suggestions. – Sid Jul 20 '18 at 15:28
  • Pentium10 - The @corpus example would not run in the web UI - which is what I have to use at work unfortunately....however the WITH example was good for now - which is what I went ahead with : WITH params AS (select ('x','y','z') as prod1, ('a','b','c') as exclude_list....(until I defined all my regularly used parameters)...), so thanks a lot for the link - it will do until I learn how to do it with a STRING udf – Sid Jul 20 '18 at 15:34
  • unfortunately using WITH to create some parameters didn't work as suggested in the link The BQ validator was green so good to go - but the 'where' statement didn't work. Tried using 'IN' instead of '=' as prod_cat1 is a list of values but was getting error message stating "expecting UNNEST...." but with '=' the validator shows green but it doesn't work Also I put "" around the parm values to avoid nesting – Sid Jul 20 '18 at 17:13
  • (THE QUERY) AS FOLLOWS: with parms AS ( SELECT "('pd001','ws008','pf003')" AS prod_cat1, "('pd001','ws008')" AS prod_cat2, "('pf003')" AS prod_cat3, "('purchase')" AS buy_trans ), mytable AS ( select id, date, prodvar from `DBtable`, parms where prodvar = prod_cat1 and event = buy_trans ) SELECT * FROM mytable – Sid Jul 20 '18 at 17:13
  • any suggestions appreciated – Sid Jul 20 '18 at 17:38