sqlfunctiongoogle-bigquerybigquery-udf

Using With Statement inside function in BigQuery


Is it possible to have a with statement inside a function in BigQuery?

I know there are several questions about this, but those are about SQL Server not a about BigQuery.

Here is what I am trying to do:

CREATE TEMP FUNCTION MyFunc(my_var INT)
RETURN INT64 AS 
(
WITH ABC AS (select * from t where t.col = var),
DEF AS (select * from t where t1.col = var),
GHI AS (select * from t where t2.col = var)

SELECT * FROM ABC JOIN DEF USING (...) JOIN GHI USING (...)
 
);

SELECT MY_FUNC(5)

However here, I get an error saying unexpected keyword WITH. I have looked at other similar questions but they are about SQL Server, which is different from my requirement in bigquery.


Solution

  • Try below:

    1. You have a typo : RETURN -> RETURNS
    2. Add one more parenthesis pair around a select statement. It will make a statement as an expression.
    3. Be sure not to return one more rows or have one more columns in your select query. Just return single INT64 value same as a return type.

    Hope this is helpful.

    CREATE TEMP FUNCTION MyFunc(my_var INT)
    RETURNS INT64 AS 
    ((
    WITH ABC AS (select * from t where t.col = var),
    DEF AS (select * from t where t1.col = var),
    GHI AS (select * from t where t2.col = var)
    
    SELECT * FROM ABC JOIN DEF USING (...) JOIN GHI USING (...)
     
    ));
    
    SELECT MY_FUNC(5);