[SOLVED] Assigning a HANA hierarchy query to a table variable leads to inconsistent results

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;

enter image description here
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;

enter image description here

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!

enter image description here

Answered By – Frederi ROSE

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.