SAP HANA Calculation View with Calculated Column with SQL Script

Question

I have created a CV just like this "Former Member" on this blog https://blogs.sap.com/2017/05/18/factory-calendar-transpose-in-sap-hana-studio-step-by-step/

It works like a charm!

My next requirement is to take that DATE_SAP for each record and determine the Accounting Month End; Yes I understand that that value will be the same for up to 30 days.

I have a function that works and gives me the accounting month end date but I cant seem to determine how to make it work with the HANA CV view I created.

Huge thanks in advance!

Here is the function

CREATE FUNCTION "MY_SCHEMA"."FN_DTACCTMONTHEND"
(
    -- Add the parameters for the function here
dtexp date
)
RETURNS dtwkend date

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS

BEGIN
    -- Declare the return variable here
    DECLARE dttemp date;
    declare cnt int;
    declare c_dtwkend date;
    
Select ADD_DAYS(to_date('19000107', 'YYYYMMDD'),(FLOOR((days_between(to_date('19000107', 'YYYYMMDD'),:dtexp) / 7)) * 7) + 7) into c_dtwkend from dummy;
cnt := 0;

    while :cnt < 6 DO
    
        dttemp := add_days(:c_dtwkend,7);
        if MONTHNAME(:dttemp) = MONTHNAME(:c_dtwkend) then
             c_dtwkend := dttemp;
        else
             dtwkend := to_date(c_dtwkend);
             return;
        end if;         
        cnt := :cnt + 1;
    end while;

    -- Return the result of the function
    dtwkend := to_date(c_dtwkend);
    return;

END;

Here is the table function:

CREATE FUNCTION "SCHEMA_NAME"."SCHEMA_NAME::FN_DTACCTMONTHEND_TEST" (DATE_SAP date ) 
    RETURNS TABLE (DTWKEND date)
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN

DECLARE dttemp date;
    DECLARE cnt int;
    DECLARE c_dtwkend date;
    DECLARE dtwkend date;


Select ADD_DAYS(to_date('19000107', 'YYYYMMDD'),(FLOOR((days_between(to_date('19000107', 'YYYYMMDD'),:DATE_SAP) / 7)) * 7) + 7) into c_dtwkend
from dummy;

cnt := 0;

    while :cnt < 6 DO
    
        dttemp := add_days(:c_dtwkend,7);
        if MONTHNAME(:dttemp) = MONTHNAME(:c_dtwkend) then
             c_dtwkend := dttemp;
        else
             dtwkend := to_date(c_dtwkend);
             return;
        end if;         
        cnt := :cnt + 1;
    end while;

    -- Return the result of the function

    dtwkend :=  to_date(c_dtwkend);
    RETURN
    SELECT dtwkend from dummy;

    
END;

Error received is SAP DBTech JDBC: [2]: general error: RETURN statement with expression should be defined for table function

Answer 1

Ok, based on the comment, the requirement is to find the last Sunday of a given month.

While the OP believes that to compute this a loop is required and that the LAST_DAY function is of no use here, the opposite is true.

The last Sunday of a month can easily be computed with the help of LAST_DAY and WEEKDAY functions:

SELECT 
     current_date
   , LAST_DAY(current_date)
   , WEEKDAY (LAST_DAY(current_date)) AS weekday_of_last_day
   --
   , ADD_DAYS (LAST_DAY(current_date)
        ,  -( (WEEKDAY(LAST_DAY(current_date)) + 1)           -- how many days back to the last Sunday?
              * sign (6 - weekday(last_day(current_date)))    -- set to 0 if the day is a SUNDAY already as THIS is the last Sunday
            )
          )    AS last_sunday_of_month
FROM 
    dummy;


CURRENT_DATE|LAST_DAY(CURRENT_DATE)|WEEKDAY_OF_LAST_DAY|LAST_SUNDAY_OF_MONTH|
------------|----------------------|-------------------|--------------------|
  2020-11-01|            2020-11-30|                  0|          2020-11-29|

---

Note: the selected columns CURRENT_DATE, LAST_DAY(current_date), and WEEKDAY_OF_LAST_DAY are included for demonstration purposes only. They are not required for the column expression LAST_SUNDAY_OF_MONTH to work.

---

In this example, I use the CURRENT_DATE but one can plug in any date - the computation works the same.

First, the WEEKDAY of the LAST_DAY of the anchor month is computed. WEEKDAY yields a number between 0 and 6 representing the weekdays starting with MONDAY, i.e. SUNDAY = 6.

If the current weekday is already 6 (= Sunday) we don't need to subtract any days to get to the last Sunday of the month. This is achieved by multiplying the offset by the SIGN of the difference between 6 (the weekday number for Sundays) and the weekday of the current date. If this difference is positive (i.e. between 1 and 6), the offset is multiplied by 1 and if the difference is zero, the offset is multiplied by 0.

This means, ADD_DAYS only "goes back to the last Sunday" when the current date is not already a Sunday.

And that's it: no loops and no SQLScript code required. This SQL expression can be used in plain SQL or in Calculation Views.

Answer 2

RETURN is reserved for the table function. Use BREAK for the while loop instead. Please find an example below.

DROP FUNCTION "SCHEMA"."FN_DTACCTMONTHEND_TEST";

CREATE FUNCTION "SCHEMA"."FN_DTACCTMONTHEND_TEST" (DATE_SAP date ) 
    RETURNS TABLE (DTWKEND date)
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN

    DECLARE dttemp date;
    DECLARE cnt int := 0;
    DECLARE c_dtwkend date;
    DECLARE dtwkend date := ADD_DAYS(to_date('19000107', 'YYYYMMDD'),(FLOOR((days_between(to_date('19000107', 'YYYYMMDD'),:DATE_SAP) / 7)) * 7) + 7);


    while :cnt < 6 DO
    
        dttemp := add_days(:c_dtwkend,7);
        if 
            MONTHNAME(:dttemp) = MONTHNAME(:c_dtwkend) 
        then
             c_dtwkend := dttemp;
        else
             dtwkend := to_date(c_dtwkend);
             BREAK;
        end if;         
        
        cnt := :cnt + 1;
    
    end while;


    -- Return the result of the function
    RETURN
        SELECT 
            to_date(c_dtwkend) AS DTWKEND
        FROM
            dummy;

    
END;