Reputation: 67
I am writing a query in bigquery using standard SQL and javascript UDF, and I ran into error "Error: Syntax error: Expected "<" but got ")"; failed to parse CREATE [TEMP] FUNCTION statement at [1:47]". Below is the command lines. Please can someone help. Many thanks.
CREATE TEMPORARY FUNCTION IRRCalc(CArray ARRAY)
RETURNS FLOAT64
LANGUAGE js AS """
function IRRCalc(CArray){
min = 0.0;
max = 1.0;
do {
guess = (min + max) / 2;
NPV = 0.0;
for (var j=0; j<CArray.length; j++){
NPV += CArray[j]/Math.pow((1+guess),j);
}
if (NPV > 0){
min = guess;
}
else {
max = guess;
}
} while (Math.abs(NPV) > 0.00000001);
return guess * 100;
}
""";
WITH Input AS
(SELECT [-100, 100, 100, 100, 100, 100] as CArray
UNION ALL
SELECT [-100, 100, 100, 100, 100] as CArray)
SELECT
CArray,
IRRCalc(CArray) as IRR
FROM Input as t;
Upvotes: 1
Views: 255
Reputation: 172993
As error statement states: Expected "<" but got ")"
I think you are just missing type in declaration
CREATE TEMPORARY FUNCTION IRRCalc(CArray ARRAY<INT64>)
instead of
CREATE TEMPORARY FUNCTION IRRCalc(CArray ARRAY)
Btw, looks like you need to do some cleaning of your JS UDF - see below
CREATE TEMPORARY FUNCTION IRRCalc(CArray ARRAY<INT64>)
RETURNS FLOAT64
LANGUAGE js AS """
min = 0.0;
max = 1.0;
do {
guess = (min + max) / 2;
NPV = 0.0;
for (var j=0; j<CArray.length; j++){
NPV += CArray[j]/Math.pow((1+guess),j);
}
if (NPV > 0){
min = guess;
}
else {
max = guess;
}
} while (Math.abs(NPV) > 0.00000001);
return guess * 100;
""";
WITH Input AS
(SELECT [-100, 100, 100, 100, 100, 100] as CArray
UNION ALL
SELECT [-100, 100, 100, 100, 100] as CArray)
SELECT
CArray,
IRRCalc(CArray) as IRR
FROM Input as t
Upvotes: 2