Error when calc IRR using Javascript UDF

Question

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;

Is CArray supposed to be an ARRAY<INT64>? Note that INT64 is not officially supported as input to JavaScript UDFs, so maybe you want ARRAY<FLOAT64>. — Elliott Brossard
– Elliott Brossard, Commented Apr 2, 2018 at 22:22
Hi Elliott - Thanks for your advise of using ARRAY<FLOAT64>. It worked! — sl007
– sl007, Commented Apr 3, 2018 at 13:39

Mikhail Berlyant · Accepted Answer · 2018-04-02 22:59:02Z

2

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

edited Apr 2, 2018 at 22:59

answered Apr 2, 2018 at 22:28

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sl007 Over a year ago

Thanks Mikhail! ARRAY<INT64> worked as a charm when the array inputs are all integers. I think in reality the cash flow would mostly be double number, so I switched to ARRAY<FLOAT64>. Anyways thanks a lot for your help.

Collectives™ on Stack Overflow

Error when calc IRR using Javascript UDF

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related