HANA数据库：Lateral Join调用表值自定义函数报错解决

问题描述 #

问题概述 #

在HANA数据库中，创建了返回单行多列数据的表值自定义函数，尝试将其用于lateral join时执行失败。

具体场景 #

自定义函数代码如下：

CREATE OR REPLACE FUNCTION  PBANALYST.F__ITEM_MBEW(
    IN
        p_str_MATNR                 NVARCHAR(18)
    ,   p_str_BWKEY                 NVARCHAR(02)
    ,   p_str_VALDATE               NVARCHAR(08)
)

RETURNS
    TABLE(
        VALDATE     NVARCHAR(08)
    ,   LBKUM       INTEGER
    ,   VERPR       DECIMAL
    ,   STPRS       DECIMAL
    )

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER


AS
BEGIN

    RETURN
        SELECT
            '20220928'  AS  VALDATE     
        ,   10          AS  LBKUM       
        ,   5.3         AS  VERPR       
        ,   10.5        AS  STPRS       
        FROM DUMMY
    ;

END;

单独调用该函数可正常返回结果，但执行包含lateral join的脚本时触发错误：

DO
BEGIN

    tbl_MATNR_LIST =
        SELECT '000000000000824151' AS MATNR , '92' AS div , '20220715' AS  VALDATE FROM dummy
        ;

    SELECT
        tbl_MATNR_LIST. *
    FROM    :tbl_MATNR_LIST     tbl_MATNR_LIST ,
    LATERAL(
        SELECT *
        FROM F__ITEM_MBEW(
                '000000000000824151'
            ,   '92'
            ,   '20220715'
            )
    ) MBEW
    ;

END;  

错误提示：

DataSource.Error : ODBC: ERROR [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;318 decimal precision specifier is out of range: -1: (1 to 38)

解决方案 #

报错根源是函数返回表中的DECIMAL类型未指定精度和刻度，HANA在执行lateral join时无法正确推断该类型的元数据，导致精度值异常（-1超出1-38的合法范围）。

修复步骤 #

1. 明确DECIMAL类型的精度和刻度：修改函数返回表中VERPR和STPRS的类型定义，根据业务需求指定具体的精度（总位数）和刻度（小数位数），例如DECIMAL(10,2)。
2. 匹配返回值类型：在函数内部的SELECT语句中，将对应字段显式转换为定义好的DECIMAL类型，确保类型一致性。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION  PBANALYST.F__ITEM_MBEW(
    IN
        p_str_MATNR                 NVARCHAR(18)
    ,   p_str_BWKEY                 NVARCHAR(02)
    ,   p_str_VALDATE               NVARCHAR(08)
)

RETURNS
    TABLE(
        VALDATE     NVARCHAR(08)
    ,   LBKUM       INTEGER
    ,   VERPR       DECIMAL(10,2)  -- 明确精度和刻度
    ,   STPRS       DECIMAL(10,2)  -- 明确精度和刻度
    )

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER


AS
BEGIN

    RETURN
        SELECT
            '20220928'  AS  VALDATE     
        ,   10          AS  LBKUM       
        ,   CAST(5.3 AS DECIMAL(10,2)) AS VERPR  -- 显式转换匹配定义类型
        ,   CAST(10.5 AS DECIMAL(10,2)) AS STPRS -- 显式转换匹配定义类型
        FROM DUMMY
    ;

END;

修改完成后，重新执行包含lateral join的脚本即可正常运行。

内容的提问来源于stack exchange，提问作者Frederi ROSE