Problems with function ( UDF ) Not recommended feature: Using SELECT INTO in Scalar UDF

[holger_blum]

Hello,

I try to create a function in HANA SP10.

This function should lookup into table T001 and return SPRAS column.

My code:

CREATE FUNCTION BUKRS2SPRAS(
 BUKRS NVARCHAR(4) )
RETURNS O_SPRAS NVARCHAR(1)
LANGUAGE SQLSCRIPT --READS SQL DATA 
AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION O_SPRAS := '#';
  SELECT T001.SPRAS INTO O_SPRAS FROM T001 T001 WHERE T001.BUKRS = :BUKRS ;
END

I get error " Not recommended feature: Using SELECT INTO in Scalar UDF"

This function should use in a normal sql like

SELECT
     X.BUKRS,
     BUKRS2SPRAS(X.BUKRS) AS SPRAS
FROM
     TABLE AS X

This is only a simple function and I know that I can left outer join table T001 on X,

I would like to understand why this does not work.

Thx for all answers.

[pfefferf · Accepted solution]

Hello Holger,

the message you get should only be a warning. Up to SPS09 a "select into" was not allowed in a scalar UDF. This restriction was removed with SPS10, but you get the warning (a "select into" clause in a scalar UDF is not the best regarding e.g. performance).

Regards,

Florian

[holger_blum · Accepted solution]

Hello Florian,

thx for your answer.

But HANA does not create this function.

Is there an other error in my function ?

UPDATE:

I try this with Toad for SAP solutions.

With HANA Studio it works....

What is the better way, in sql,  without left outer join ?

Regards,

Holger

[chri_schmitt · Accepted solution]

Hello,

I hit the same issue with HANA DB 1.00.110.00.1447753075 when trying to compile a UDF using SELECT/INTO:

Warning: * 1347: Not recommended feature: Using SELECT INTO in Scalar UDF SQLSTATE: HY000

This warning prevented me to create the UDF.

Rob, a SAP colleague, pointed me out several options. I was able to create the UDF by setting up following options:

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') = 'silent' with reconfigure

;

Thanks to Rob.

Cheers Christian