Skip to Content
0
Oct 09, 2019 at 06:51 AM

Scalar-valued function with dynamic column selection

170 Views

Hello,

I would like to do something like this in a scalar-valued function (hdbscalarfunction) oin HANA in order to get back the average value of a key figure with respect to a certain characteristic:

FUNCTION "JPCHURN"."ml.jpchurn.functions::getAveFeature" ( in_clsID Integer , in_featureName nvarchar(256) ) 
	RETURNS ex_aveValue Double
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
/***************************** 
	Write your function logic
 *****************************/
declare lv_String nvarchar(256);
lv_String = 'a.' || :in_featureName;

 select (:lv_String) into ex_aveValue
 from "JPCHURN"."ml.jpchurn.data::jpChurn.tab_DataRaw" as a 
 inner join "JPCHURN"."CustChurnAPLModelsvClust_01_resultTrain" as b
 on a."AccountID" = b."AccountID"
 where b.closest_cluster_1 = :in_clsID;

However the dynamic select for :lv_string which actually contains the name of the field does not work. I could do it as a workaround with an if-else branch and then typing in the names of the fields manually. However beside this is only pseudo-dynamic I have ~ 25 fields which could also change.

Is there any idea how to achieve this? Dynamic SQL is btw not allowed in scalar-valued functions.

Thanks + Regards, Ingo