'Substitute a common HANA sub-select with a Scalar Function?

We replaced an often used sub-select with a scalar function using SELECT INTO. The passed arguments make sure that the resultset always contains only a single integer:

CREATE FUNCTION MAT_AVAIL (DocEntry INT, LineNum INT)
RETURNS VAL INT
LANGUAGE SQLSCRIPT AS
BEGIN
  SELECT
    CASE
      WHEN OWOR."Status" IN ('L', 'C') THEN 3
      WHEN OITW."OnHand" < WOR1."PlannedQty" THEN 2
      WHEN OITW."OnHand" < OITW."IsCommited" THEN 1
      ELSE 0
    END
  INTO VAL
  FROM WOR1
    INNER JOIN OWOR ON OWOR."DocEntry" = WOR1."DocEntry"
    LEFT JOIN OITW ON OITW."ItemCode" = WOR1."ItemCode" AND OITW."WhsCode" = WOR1."wareHouse"
    INNER JOIN OITM ON WOR1."ItemCode" = OITM."ItemCode"
  WHERE WOR1."DocEntry" = :DocEntry AND WOR1."LineNum" = :LineNum;
END;

This is working flawlessly with MSSQL and luckily since a couple of releases also for HANA, but it stays special as its "warning" about the SELECT INTO is actually an exception when trying to execute the function with the DI-API Recordset:

Not recommended feature: Using SELECT INTO in Scalar UDF

So, we have to ask our customers to manually work around this disability, like mentioned here:

alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'enable_select_into_scalar_udf') = 'true' with reconfigure;
alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'sudf_support_level_select_into') = 'silent' with reconfigure;
alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'dynamic_sql_ddl_error_level') = '0' with reconfigure;

We want to get rid of this annoyance, so the question is simple: What is the HANA recommended approach to substitute a common sub-select that returns a scalar value?

NB: While researching, I stumbled over performance discussions with regard to Scalar UDFs with HANA, but even if improving speed with an alternative approach would be welcome, this is not a decisive point here.

hanasql-functionsap-business-one-di-api


Solution 1:[1]

It may be helpful to have a small reproducible example. I have tried the following on my HANA Cloud system without any warning or errors:

CREATE OR REPLACE FUNCTION TEST (inval INT)
RETURNS outval INT
LANGUAGE SQLScript AS
BEGIN
    SELECT :inval INTO outval FROM DUMMY;
END;

SELECT TEST(5) FROM DUMMY;

Does this example capture your issue?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mathias Kemeter

Related Questions