Cannot create scalar function with SUBSTR_REGEXPR

[roland_bouman]

Hi all,

when I run this create function statement:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    found := substr_regexpr(:p_pattern in :p_txt);
end;

I get this error message:

Started: 2016-10-19 10:25:34
Could not execute 'create function f_test_regexp( p_txt NVARCHAR(5000) , p_pattern NVARCHAR(64) ) returns found ...' in 64 ms 267 µs . 
SAP DBTech JDBC: [8]: invalid argument: exception: CompilationFailedException: No details
Program : 

#pragma function "\"NLRBVD\".\"F_TEST_REGEXP\""
export Void main(NullString "P_TXT" sP_TXT, NullString "P_PATTERN" sP_PATTERN, NullString "FOUND" & sFOUND)
{
    RSHeap heap = RSHeap();
    RSEnv env = RSEnv(heap);
    Int32 ucnt = 0;
    // scalar input parameter: P_TXT
    RSNString vP_TXT = RSNString( sP_TXT , env );
    // scalar input parameter: P_PATTERN
    RSNString vP_PATTERN = RSNString( sP_PATTERN , env );
    RSNString vFOUND = expr::null_<RSNString>(); // scalar output parameter
    RSNString vFOUND___2; // FOUND_2: intermediate def-variable
    #pragma location " line 8 col 5 (at pos 129)"
    {
        RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
    tFOUND.checkLengthLEAndThrow(5000z);
        vFOUND = tFOUND;
    }
    sFOUND = NullString(vFOUND);
    return;
}
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): Error: Function 'expr::substr_regex_<RSNString>(RSEnv, RSNString, RSNString, RSInteger, RSInteger, RSInteger, RSNString)' not found
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
                                                                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

But when I rewrite it like this:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    select substr_regexpr(:p_pattern in :p_txt)<br>    into found<br>    from dummy;
end;

I only get a warning:

java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF

but the function seems to work fine.

So, how come? SAP/HANA, have you been drinking?

[lbreddemann]

Ok, SQL Script is not a superset of SQL, which also means that not all SQL functions will just work in SQL Script statements (like the assignment you used).

SELECT however is a supported command and it provides the SQL context which in turn gives access to all SQL functions.

The warning really is just that: a warning about possible unacknowledged side effects (worse performance and higher resource consumption in this case).