CODEWITHSUNDEEP
google-bigquery
Aced
Aced

Reputation: 109

Function in Google Big Query

I am trying to create a function in google biq query, but i m getting the below error. any help is appreciated:

CREATE FUNCTION `test.xx.x_to_y_Id`(ID INT64)
RETURNS INT64

    SELECT CASE 
  WHEN CAST(FLOOR(ID/POWER(CAST(2 AS INT64),38)) AS INT64) & 3 = 0
    THEN ID & CAST(12345678 AS INT64)
    ELSE ID END

Syntax error: Expected "(" or string literal but got keyword SELECT at [4:9]

Upvotes: 0

Views: 97

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is fixed version of your function 

#standardSQL
CREATE TEMP FUNCTION test(ID INT64)
RETURNS INT64 AS ((
  SELECT CASE 
    WHEN CAST(FLOOR(ID/POWER(CAST(2 AS INT64),38)) AS INT64) & 3 = 0
    THEN ID & CAST(12345678 AS INT64)
    ELSE ID 
  END
));
SELECT test(12)

As you can see - you were missing some aspects of it like TEMP keyword, some open and close brackets, etc.

Meantime, as in simple scenario like yours - you can omit use of SELECT and just use expression alone like in below example (note in this case you also getting rid of extra open/close brackets 

#standardSQL
CREATE TEMP FUNCTION test(ID INT64)
RETURNS INT64 AS (
  CASE 
    WHEN CAST(FLOOR(ID/POWER(CAST(2 AS INT64),38)) AS INT64) & 3 = 0
    THEN ID & CAST(12345678 AS INT64)
    ELSE ID 
  END
);
SELECT test(12)

Upvotes: 1

Tamir Klein
Tamir Klein

Reputation: 3642

You can use this as the CREATE function statement

CREATE TEMP FUNCTION
  test(ID FLOAT64) 
AS ( 
CASE 
WHEN CAST(FLOOR(ID/POWER(CAST(2 AS INT64),38)) AS INT64) & 3 = 0
  THEN ID & CAST(12345678 AS INT64)
ELSE ID END);

Note: I removed the SELECT from the body of the function and corrected the function signature to match BigQuery function style as defined here

Upvotes: 2

Related Questions