Table of Contents
Issue
Context
I want to use the HANA HIERARCHY_TEMPORAL function to work on a SAP KNVH hierarchy with time intervals ;
When simply used in a SELECT query, this works fine ;
When otherwise using the very same query but assigning it to a table variable, the result is inconsistent.
My problem
This anonymous block works just fine using an HANA HIERARCHY_TEMPORAL function :
DO
BEGIN
DECLARE hierarchy_type CHAR(1);
DECLARE valid_from CHAR(8);
DECLARE valid_until CHAR(8);
hierarchy_type = 'A' ;
valid_from = '20211201' ;
valid_until = '20211201' ;
SELECT
HIERARCHY_RANK ,
HIERARCHY_TREE_SIZE ,
HIERARCHY_PARENT_RANK ,
HIERARCHY_ROOT_RANK ,
HIERARCHY_LEVEL ,
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR ,
HKUNNR ,
node_id ,
parent_id ,
valid_from ,
valid_until ,
DATAB ,
DATBI
FROM HIERARCHY_TEMPORAL (
SOURCE
(
SELECT
HIERARCHY_COMPOSITE_ID(
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR
) AS node_id ,
CASE HKUNNR
WHEN '' THEN NULL
ELSE HIERARCHY_COMPOSITE_ID(
HITYP ,
HVKORG ,
HVTWEG ,
HSPART ,
HKUNNR
)
END AS parent_id ,
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR ,
HKUNNR ,
DATAB AS valid_from ,
DATBI AS valid_until ,
DATAB ,
DATBI
FROM SAPKTP. KNVH
WHERE
KNVH. HITYP = :hierarchy_type
)
VALID FROM :valid_from UNTIL :valid_until
);
END;
But assigning the very same block and querying the table variable afterwards leads to inconsistent results :
DO
BEGIN
DECLARE tbl_CLIENT_HIERARCHY TABLE (
HIERARCHY_RANK CHAR ,
HIERARCHY_TREE_SIZE CHAR ,
HIERARCHY_PARENT_RANK CHAR ,
HIERARCHY_ROOT_RANK CHAR ,
HIERARCHY_LEVEL CHAR ,
HITYP CHAR ,
VKORG CHAR ,
VTWEG CHAR ,
SPART CHAR ,
KUNNR CHAR ,
HKUNNR CHAR ,
node_id CHAR ,
parent_id CHAR ,
valid_from CHAR ,
valid_until CHAR ,
DATAB CHAR ,
DATBI CHAR
);
DECLARE hierarchy_type CHAR(1);
DECLARE valid_from CHAR(8);
DECLARE valid_until CHAR(8);
hierarchy_type = 'A' ;
valid_from = '20211201' ;
valid_until = '20211201' ;
tbl_CLIENT_HIERARCHY =
SELECT
HIERARCHY_RANK ,
HIERARCHY_TREE_SIZE ,
HIERARCHY_PARENT_RANK ,
HIERARCHY_ROOT_RANK ,
HIERARCHY_LEVEL ,
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR ,
HKUNNR ,
node_id ,
parent_id ,
valid_from ,
valid_until ,
DATAB ,
DATBI
FROM HIERARCHY_TEMPORAL (
SOURCE
(
SELECT
HIERARCHY_COMPOSITE_ID(
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR
) AS node_id ,
CASE HKUNNR
WHEN '' THEN NULL
ELSE HIERARCHY_COMPOSITE_ID(
HITYP ,
HVKORG ,
HVTWEG ,
HSPART ,
HKUNNR
)
END AS parent_id ,
HITYP ,
VKORG ,
VTWEG ,
SPART ,
KUNNR ,
HKUNNR ,
DATAB AS valid_from ,
DATBI AS valid_until ,
DATAB ,
DATBI
FROM SAPKTP. KNVH
WHERE
KNVH. HITYP = :hierarchy_type
)
VALID FROM :valid_from UNTIL :valid_until
);
SELECT TOP 10 * FROM :tbl_CLIENT_HIERARCHY;
END;
Does anybody know why?
Thank you for your help.
Solution
Actually the problem had nothing to do with HANA hierarchy : I simply didn’t precise between brackets the number of characters for the declared table variable, which is then interpreted as CHAR(1), leading to the strange extraction result!
Answered By – Frederi ROSE
Answer Checked By – Dawn Plyler (BugsFixing Volunteer)