BigQuery JavaScript UDF to call Array with values from SQL query

Question

I am trying to write my First Bigquery UDF function (converting Oracle Function) and thanks to SO for the help so far I am able to write partial code as shown below,

My task is when we pass the parameters like admit_date, input1, input2 are passed and discharge date should be calculated based on few calculations (not weekends or Holidays).

Apologies for any mistakes or any trouble

Here is my code :

    DECLARE list ARRAY <STRING>;
    DECLARE v_holiday STRING DEFAULT 'No';
    DECLARE v_due_report INT64;
    DECLARE v_due_report_cnt INT64;
    DECLARE v_days INT64;
    DECLARE p_date DATE;
    DECLARE p_input1 STRING;
    DECLARE p_input2 STRING;
    DECLARE v_bus_day STRING;
    DECLARE v_cnt INT64 DEFAULT 1;
    DECLARE v_max_days INT64 DEFAULT 1;
    
    #SET v_holiday = (SELECT CASE WHEN EXISTS (SELECT 1 FROM `examples.date_calendar` WHERE HOLIDAY = DATE(p_date)) THEN 'Yes' ELSE 'No' END);
    
    select ARRAY(SELECT holiday from `examples.date_calendar`) AS list;
    
    SET v_due_report_cnt = (SELECT COUNT(*) FROM `examples.report_assessments` WHERE p_date BETWEEN START_DATE AND END_DATE 
                                    AND p_input1 = ICD_9_CLASS AND p_input2 = LAST_MEDICATION);
    
    CREATE TEMP FUNCTION patient_report_date(p_admit_date DATE, p_input1 STRING,p_input2 STRING)
     RETURNS STRING
     LANGUAGE js AS """
         var days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
         var dayName = days[p_admit_date.getDay()];
    
        if(p_admit_date < Date.parse("2016-02-01") {
                v_bus_day = p_admit_date.setDate(date.getDate() + 2);
                return v_bus_day;
          }
    
        # SET v_HOLIDAY = (SELECT CASE WHEN EXISTS (SELECT 1 FROM `examples.date_calendar` WHERE HOLIDAY = TRUNC(p_admit_date)) THEN 'Yes' ELSE 'No' END)
    
          if(!(dayName) in ('Saturday', 'Sunday')) && (v_holiday == 'No'){
              v_days = 2;
          }
           else { 
               v_days = 3;
                          p_date_trunc_time = new Date(p_date.getFullYear(), p_date.getMonth(), p_date.getDate());
           v_bus_day = new Date(p_date_trunc_time.setSeconds(p_date_trunc_time.getSeconds() + 1));
          }
    
           if(p_admit_date < Date.parse("2020-07-01") {
               v_due_report = 0
           }
          #SET v_due_report_cnt = (SELECT COUNT(*) FROM `examples.report_assessments` WHERE p_admit_date BETWEEN START_DATE AND END_DATE 
                                    #AND p_input1 = ICD_9_CLASS AND p_input2 = LAST_MEDICATION);
            else if (v_due_report_cnt > 0) {
                  v_due_report = 4
                 }
          v_days = v_days + v_due_report;    
    
        while max_days <= v_days {
            if (! days[(p_admit_date + day).getDay()] in ('Saturday','Sunday') && (list.indexOf(p_admit_date)==-1) {
                v_cnt++
                max_days++
            } 
            day++
           
        }
    
    v_bus_day = p_admit_date + v_vcnt;
    
     return v_bus_day
    
    """;
WITH data_date AS (
  SELECT DATE("2021-05-31") AS p_DATE, "ICD8081" AS p_input1, "CDE2" AS p_input2 UNION ALL
  SELECT DATE("2021-05-30"),"ICD8211","DER2"
)
SELECT p_DATE,
       p_input1,
       p_input2,
  patient_report_date(p_DATE,p_input1,p_input2) discharge_date
FROM data_date;

When I executed the function it throwed me error "SyntaxError: Unexpected Token" any suggestions or feedback on this code would be helpful.
Appreciate your help
Thanks for reading my post.

Updated Code: Finally, I am able to get my code working, want to add in SO so it might be useful to someone.

DECLARE holiday_list ARRAY <STRING>;
DECLARE holiday_weekend ARRAY <STRING>;
DECLARE report_assessments ARRAY <STRUCT<ICD_9_CLASS STRING,LAST_MEDICATION STRING>>;
DECLARE p_admit_date DATE;
DECLARE p_input1 STRING;
DECLARE p_input2 STRING;
   
SET holiday_list =  (SELECT ARRAY(SELECT CAST(holiday AS STRING) FROM `examples.date_calendar`)); 
SET v_due_report = (SELECT ARRAY(SELECT STRUCT(CAST(ICD_9_CLASS AS STRING) AS ICD_9_CLASS, CAST(LAST_MEDICATION AS STRING) AS LAST_MEDICATION) FROM `examples.report_assessments`));
    
CREATE TEMP FUNCTION patient_report_date(p_admit_date DATE, p_input1 STRING,p_input2 STRING)
   RETURNS STRING
     LANGUAGE js AS """
        var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
        var holiday_weekend = ['Saturday','Sunday'];
        var v_signature=0;
        var v_days=0;
        var v_day =1;
        var v_cnt=0;
        var v_max_days=1;
    
        if(p_admit_date < new Date("2016-02-01")) {
                 p_admit_date.setDate(p_admit_date.getDate() + 2); 
                }
        else 
            {
              if((holiday_weekend.indexOf(days[p_admit_date.getDay()]) === -1) && (holiday_list.indexOf(p_admit_date) === -1)) {
                    v_days = 2; 
                    }
              else {
                    v_days = 3;
                    }
    
              for(var i = 0; i < report_assessments.length; i++) {
                    if ((report_assessments[i]['ICD_9_CLASS'] === p_input1)  && (signature_assessments[i]['LAST_MEDICATION'] === p_input2)) { 
                        v_signature++; 
                  } 
              } 
            
             if (p_admit_date < Date("2020-07-01") && (v_signature === 0)) {
                    v_days = v_days; 
                    }
             else if (v_signature > 0) {
                    v_days = v_days + 4; 
                    }

             function addDays(date, days) {
             var result = new Date(date);
             result.setDate(result.getDate() + days);
             return result;
            }
            while max_days <= v_days {
                if ((holiday_list.indexOf(addDays(p_admit_date,v_day).toISOString().slice(0, 10)) === -1) && (holiday_weekend.indexOf(days[addDays(p_admit_date,v_day).getUTCDay()]) === -1)) { 
                    v_max_days++;
                    }  
                v_day++;
                v_cnt++;
                } 
            p_admit_date.setDate(p_admit_date.getDate() + v_cnt); 
            } 

        return  p_admit_date;
    
    """;
    
WITH data_date AS (
  SELECT DATE("2021-05-31") AS p_DATE, "ICD8081" AS p_input1, "CDE2" AS p_input2,holiday_list, report_assessments UNION ALL
  SELECT DATE("2021-05-30"),"ICD8211","DER2",holiday_list, report_assessments
)
SELECT p_DATE,
       p_input1,
       p_input2,
  patient_report_date(p_DATE,p_input1,p_input2,holiday_list, report_assessments) discharge_date
FROM `examples.tabledata`; ```

Sergey Geron · Accepted Answer · 2021-05-31 14:28:50Z

1

Yes, javascript UDFs are also persistent. Just remove TEMP and optinally specify project name and dataset name:

CREATE FUNCTION project_name.dataset_name.patient_report_date(...)

answered May 31, 2021 at 14:28

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

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

7 Comments

Rakesh007 Over a year ago

Thanks @Sergey, I want to convert to pass values into UDF from above array

Sergey Geron Over a year ago

JavaScript UDF can receive arrays as well - cloud.google.com/bigquery/docs/reference/standard-sql/…

Sergey Geron Over a year ago

Example - stackoverflow.com/questions/65738242/…

Rakesh007 Over a year ago

Thanks a lot Sergey, quick question can I use list.indexOf(p_admit_date)==-1 to check if the date exists in the array declared above

Rakesh007 Over a year ago

I have updated the code with sample select statements, when I executed in BigQuery it failed and not sure how to debug, can you please help me with this issue

|

Collectives™ on Stack Overflow

BigQuery JavaScript UDF to call Array with values from SQL query

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related