CODEWITHSUNDEEP
google-bigquery
Bruce 'Batman' Wayne
Bruce 'Batman' Wayne

Reputation: 35

Bigquery: How to write Multiline SQL UDF?

I would like to create a SQL UDF in bigquery where I could perform certain operations and then return the value.

For eg, In SQL Server, I could create a UDF as follows:

CREATE FUNCTION east_or_west (
    @long DECIMAL(9,6)
)
RETURNS int AS
BEGIN
    DECLARE @return_value int;
    SET @val = 5;
    IF (@long > 0.00) SET @val = 10;
    IF (@long < 0.00) SET @val = 20;
    SET @ModelID = (SELECT count(*) 
                  FROM MODELS m
                 WHERE m.areaid = @val)
 
    RETURN @ModelID
END;

How would I go around implementing this simple multiline sql udf in bigquery?

Upvotes: 0

Views: 812

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173106

Use below

CREATE FUNCTION east_or_west(long float64) as ((
  SELECT COUNT(*) 
  FROM `project.dataset.MODELS` m
  WHERE m.areaid = (
    CASE 
      WHEN long > 0.00 THEN 10
      WHEN long < 0.00 THEN 20
      ELSE 5
    END
  )
));

Upvotes: 0

Related Questions