Using With Statement inside function in BigQuery

Question

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.

Answer 1

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);