CODEWITHSUNDEEP
google-bigquery
denim
denim

Reputation: 463

BigQuery: define variables inside a function

I am trying to define a simple Standard SQL function where the input is multiplied by a constant defined inside the function.

CREATE or REPLACE FUNCTION Test(b FLOAT64) 
RETURNS FLOAT64
AS
DECLARE var FLOAT64 = 0.5;
RETURN (b * var)
;

the syntax error is

Syntax error: Expected "(" or string literal but got keyword DECLARE

How do you define variables inside functions?

Upvotes: 3

Views: 6881

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Another option for BigQuery Standard SQL using SQL UDF

#standardsql
create or replace function test(b float64) 
returns float64 as ((
with variables as (
  select 0.5 as var1, 1.5 as var2
)
select b * var1 + var2
from variables
));

As you can see - you can define variables CTE with all needed variables in one row

Upvotes: 6

Sergey Geron
Sergey Geron

Reputation: 10232

Variables are not supported inside SQL functions in BigQuery. One of the options would be to declare and set the variable in the beginning of your code. Then pass it as an argument to a function.

Another options would be to use Javascript Function:

CREATE TEMP FUNCTION customGreeting(b FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  var f = 0.5;
  return f * b;
""";

Upvotes: 3

Related Questions