通过TABLE_FUNCTION创建HANA计算视图

一、创建TABLE_FUNCTION

1. 创建

在SAP HANA Development模式的Repositories目录中选择新建-->OTHER-->选择Table Function

 Next—>命名- ->Finish

2. 代码编辑

在代码页面编写逻辑,完成后激活(RETURNS后也可以是一张数据库中的表) 

3. 创建计算视图

3.1 添加数据来源，搜索对应的Table Function

3.2 将TABLE_FUNCTION的变量与计算视图变量对应，在计算视图的Input Parameters上右键，选择Manage Mappings,在管理页面将变量推拽到计算视图中，如果变量名称不一致也可手工推拽对应，点OK完成

3.3. 激活计算视图，完成创建

4.变量多值传参

4.1 实现变量多值传参(拼接变量值)

FUNCTION "HA_MOD"."ZTEMP_REQUIREMENT.TEST::CVS_TABLE_FUNCTION" (ZLOGSYS_V NVARCHAR(5000), ZBEG NVARCHAR(8) ,  ZEND NVARCHAR(8),ZCOM_CODE_V NVARCHAR(5000)) 
    RETURNS TABLE (
     "ZLOGSYS" NVARCHAR(50),
     "0CALDAY" NVARCHAR(100) ,
     "ZCOM_CODE" NVARCHAR(100),
     "ZCOM_CODE___T" NVARCHAR(100)
     )        
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER 
 
    AS
BEGIN
 
--当参数多值时必输时
--DECLARE  RE_ZCOM_CODE_V NVARCHAR(5000) :='ZCOM_CODE IN ('||:ZCOM_CODE_V||')' ;
 
--当有多个参数是多值必输时
--DECLARE  RE_VALUES_V NVARCHAR(5000) :=' ZCOM_CODE IN ('||:ZCOM_CODE_V||') AND ZLOGSYS IN ('||:ZLOGSYS_V||') ' ;
 
--当参数是多值可选时
/*DECLARE  RE_ZCOM_CODE_V NVARCHAR(5000) ;
SELECT 
     CASE WHEN :ZCOM_CODE_V = ''
          THEN '1=1'
          ELSE 'ZCOM_CODE IN ('||:ZCOM_CODE_V||')' END 
   INTO RE_ZCOM_CODE_V FROM DUMMY ;
*/
--当有多个参数是多值可选时   
DECLARE  RE_ZCOM_CODE_V NVARCHAR(5000);
DECLARE  RE_ZLOGSYS_V NVARCHAR(5000);
DECLARE  RE_VALUES_V NVARCHAR(5000);
SELECT 
     CASE WHEN :ZLOGSYS_V = ''
          THEN '1=1'
          ELSE 'ZLOGSYS IN ('||:ZLOGSYS_V||')' END 
   INTO RE_ZLOGSYS_V  FROM DUMMY ;
 
SELECT 
     CASE WHEN :ZCOM_CODE_V = ''
          THEN '1=1'
          ELSE 'ZCOM_CODE IN ('||:ZCOM_CODE_V||')' END 
   INTO RE_ZCOM_CODE_V  FROM DUMMY ;
  
  RE_VALUES_V := ''||:RE_ZLOGSYS_V||' AND '||:RE_ZCOM_CODE_V||'';
 
 
TAB1 = SELECT "ZLOGSYS",
              "0CALDAY",    
              "ZCOM_CODE",    
              "ZCOM_CODE___T" 
        FROM "_SYS_BIC"."system-local.bw.bw2hana/ZRSDCP02"('PLACEHOLDER' = ('$$keydate$$','20200601'),
             'PLACEHOLDER' = ('$$language$$','1'),'PLACEHOLDER' = ('$$mp_pruning_active$$','1'))    
        where "0CALDAY" BETWEEN :ZBEG  AND :ZEND 
        GROUP BY 
              "ZLOGSYS",
              "0CALDAY",    
              "ZCOM_CODE",    
              "ZCOM_CODE___T" ;
 
TAB2 = APPLY_FILTER (:TAB1,:RE_VALUES_V) ;
 
RETURN 
      SELECT "ZLOGSYS",
             "0CALDAY",    
        
	 "ZCOM_CODE",    
             "ZCOM_CODE___T" 
        FROM :TAB2   ;
 
END;

4.2. 实现变量多值传参(截取变量值)，需要创建两个TABLE_FUNCTION，一个用于获取处理变量值，一个用于查询数据

4.2.1 获取处理变量值

FUNCTION "HA_MOD"."ZTEMP_REQUIREMENT.TEST::ZTF_DUMMY" ( ZCOM_CODE_V NVARCHAR(5000))

    RETURNS TABLE (

        ZCOM_CODE NVARCHAR(5000)

        )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER AS

BEGIN

DECLARE COUNTER INT :=1 ;

RE_VALUES = SELECT SUBSTR_BEFORE (:ZCOM_CODE_V,',') AS ZVALUE FROM DUMMY;

SELECT SUBSTR_AFTER (:ZCOM_CODE_V,',')||',' INTO ZCOM_CODE_V FROM DUMMY;

WHILE (LENGTH (:ZCOM_CODE_V) > 0)
   DO
      RE_VALUES =

        SELECT SUBSTR_BEFORE (:ZCOM_CODE_V,',') ZVALUE FROM DUMMY

            UNION

        SELECT ZVALUE FROM :RE_VALUES;

    SELECT SUBSTR_AFTER (:ZCOM_CODE_V,',') INTO ZCOM_CODE_V FROM DUMMY;

 END WHILE ;

 RETURN
 SELECT REPLACE (ZVALUE,'''','') AS ZCOM_CODE FROM :RE_VALUES ;
END;

4.2.2 基于变量查询数据

FUNCTION "HA_MOD"."ZTEMP_REQUIREMENT.TEST::ztf_dummy1" (ZCOM NVARCHAR(5000), ZBEG NVARCHAR(8) ,ZEND NVARCHAR(8) )
    RETURNS TABLE (

     "ZLOGSYS" NVARCHAR(50),

     "0CALDAY" NVARCHAR(100) ,

     "ZCOM_CODE" NVARCHAR(100),

     "ZCOM_CODE___T" NVARCHAR(100)

     )       

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    AS

BEGIN
return
 SELECT "ZLOGSYS",
        "0CALDAY",   
        "ZCOM_CODE",   
        "ZCOM_CODE___T"
   FROM "_SYS_BIC"."system-local.bw.bw2hana/ZRSDCP02"('PLACEHOLDER' = ('$$keydate$$','20200601'),'PLACEHOLDER' = ('$$language$$','1'),'PLACEHOLDER' = ('$$mp_pruning_active$$','1'))   
   where "0CALDAY" BETWEEN :ZBEG  AND :ZEND
   and "ZCOM_CODE" in
              (
                  select  "ZCOM_CODE"
                  from "HA_MOD"."ZTEMP_REQUIREMENT.TEST::ZTF_DUMMY"(:ZCOM)
              )
   GROUP BY
              "ZLOGSYS",
              "0CALDAY",   
              "ZCOM_CODE",   
              "ZCOM_CODE___T" ;
END;

5.查询TABLE_FUNCTION

如果有变量需要在最后添加 ( '变量1的值' ，'变量2的值',…….. )

SELECT  * FROM "_SYS_BIC"."ZTEMP_REQUIREMENT.TEST::TABLE_FUNCTION_ZTEST040119_2"('20230101','20230101')

本文内容来自于网络资料和个人理解，仅供参考。