Hi all,

I this short blog post, I would like share a few useful CTE examples.

Sometimes we require complex sql queries and at that point we have a few options,

• CDS
• AMDP
• CTE

By considering, DRY principle, if we will use it in multiple places, we may prefer to create cds or amdp depending on requirement. But if our requirement is to use it once, than we can use CTE.

Below there are  2 examples of CTE.

First example, 2 queries with group by expression consumed in main query.

WITH
    +lastinit AS (
      SELECT class, MAX( id ) AS id FROM ztg_zii_t_json
        WHERE json_type EQ @c_json_type-initial
          AND ( status EQ @c_process_status-completed OR status EQ @c_process_status-partially_completed )
       GROUP BY class
     ) ,

    +maxtree AS (
      SELECT class, v_tree_order , MAX( pvname ) AS pvname FROM ztg_zii_t_mbom GROUP BY class ,v_tree_order
    )

    SELECT
       m~node_id
      ,m~class
      ,m~json_sap_id
      ,m~v_tree_order
      ,m~pname
      ,m~pvname
      ,m~variant_status
      ,m~pnguid
      ,m~pntype
      ,m~parent_node_id
      ,m~parent_pnguid
      ,m~parent_pname
      ,m~parent_ntype
      ,m~rel_guid
      FROM ztg_zii_t_mbom AS m
      INNER JOIN ztg_zii_t_json AS j ON j~id EQ m~json_sap_id
      INNER JOIN +maxtree AS x ON x~v_tree_order EQ m~v_tree_order AND x~pvname EQ m~pvname
      INNER JOIN +lastinit AS i ON i~class EQ m~class AND i~id LE m~json_sap_id
      INTO TABLE @ref_records_via_treeorder.

Second example, a recursive CTE query to build child to parent tree.

Thanks to Enes for sample code.

CLASS zlcl_cl_cte_bm DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.

    TYPES: BEGIN OF ty_parent,
             node       TYPE ztg_zii_t_mbom-node_id,
             pnguid        TYPE ztg_zii_t_mbom-pnguid,
             parent_pnguid TYPE ztg_zii_t_mbom-parent_pnguid,
           END OF ty_parent,
           tt_parent TYPE TABLE OF ty_parent.

    CLASS-METHODS get_parents
      IMPORTING
        i_pnguid  TYPE ztg_zii_t_mbom-pnguid
      EXPORTING
        e_results TYPE tt_parent.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS ZLCL_CL_CTE_BM IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZLCL_CL_CTE_BM=>GET_PARENTS
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_PNGUID                       TYPE        ZTG_ZII_T_MBOM-PNGUID
* | [<---] E_RESULTS                      TYPE        TT_PARENT
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_parents.

    WITH +parenthiearchy AS
      ( SELECT node_id as node ,pnguid ,parent_pnguid FROM ztg_zii_t_mbom )
      WITH ASSOCIATIONS ( JOIN TO MANY +parenthiearchy AS ph ON +parenthiearchy~pnguid = ph~parent_pnguid )

    SELECT DISTINCT node,pnguid ,parent_pnguid
    FROM HIERARCHY( SOURCE +parenthiearchy CHILD TO PARENT ASSOCIATION ph
                    START WHERE pnguid EQ @i_pnguid
                    SIBLINGS ORDER BY pnguid
                    MULTIPLE PARENTS ALLOWED )
    APPENDING CORRESPONDING FIELDS OF TABLE @e_results.


  ENDMETHOD.
ENDCLASS.

And here is the Abap code for recursive method call which also creates child to parent tree.

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZTG_ZII_CL_MBOM_UTIL=>GET_PARENT_NODES
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_PNAME                        TYPE        PNODID-PNAME(optional)
* | [--->] I_PNGUID                       TYPE        PNODID-PNGUID(optional)
* | [--->] I_LEVEL                        TYPE        I(optional)
* | [--->] I_LEVEL_LIMIT                  TYPE        I(optional)
* | [<---] E_PARENT_NODES                 TYPE        TT_PARENT_NODE
* | [<-()] R_STATE                        TYPE        ZTG_ZII_S_STATE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_parent_nodes.
    TRY.

        IF i_pname IS INITIAL AND i_pnguid IS INITIAL.
          r_state-status = c_stat-warning.
          MESSAGE s122(ztg_zii) INTO r_state-status_text.
        ELSE.

          DATA: r_pname  TYPE ty_r_pname,
                r_pnguid TYPE ty_r_pnguid.

          IF i_pname IS NOT INITIAL.
            APPEND VALUE #( sign = 'I' option = 'EQ' low = i_pname ) TO r_pname.
          ENDIF.

          IF i_pnguid IS NOT INITIAL.
            APPEND VALUE #( sign = 'I' option = 'EQ' low = i_pnguid ) TO r_pnguid.
          ENDIF.

          SELECT p~pname ,p~pnguid
                ,pc~pnguid AS parent_pnguid ,pc~pname AS parent_pname
            FROM pnodid AS p
            LEFT OUTER JOIN prelid AS r ON r~guid2 EQ p~pnguid
            LEFT OUTER JOIN pnodid AS pc ON pc~pnguid EQ r~guid1
            WHERE p~pname IN @r_pname
              AND p~pnguid IN @r_pnguid
            INTO TABLE @DATA(parents).
          IF sy-subrc IS INITIAL.

            LOOP AT parents ASSIGNING FIELD-SYMBOL(<fs>).
              APPEND VALUE ty_parent_node(
                  pnguid       = <fs>-pnguid
                  pname        = <fs>-pname
                  parent_pname  = <fs>-parent_pname
                  parent_pnguid = <fs>-parent_pnguid
                  level        = i_level
              ) TO e_parent_nodes.
            ENDLOOP.

            i_level += 1.
            IF i_level_limit IS INITIAL OR
              ( i_level_limit IS NOT INITIAL AND i_level < i_level_limit ).


              DATA: tmp_nodes TYPE tt_parent_node.
              LOOP AT e_parent_nodes ASSIGNING FIELD-SYMBOL(<fse>)
                WHERE parent_pnguid IS NOT INITIAL.
                get_parent_nodes(
                  EXPORTING
                    i_pnguid      = <fse>-parent_pnguid
                    i_level       = i_level
                    i_level_limit = i_level_limit
                  IMPORTING
                    e_parent_nodes = DATA(parents2)
                  RECEIVING
                    r_state       = <fse>-parent_status
                ).

                APPEND LINES OF parents2 TO tmp_nodes.
                REFRESH parents2.

                IF <fse>-parent_status NE c_stat-success.
                  APPEND VALUE ty_parent_node(
                                    pnguid       = <fse>-parent_pnguid
                                    pname        = <fse>-parent_pname
                                    level        = i_level
                                    parent_status-status = c_stat-info
                                ) TO tmp_nodes.
                ENDIF.

              ENDLOOP.

              APPEND LINES OF tmp_nodes TO e_parent_nodes.
            ENDIF.
          ENDIF.

          IF e_parent_nodes IS NOT INITIAL.
            r_state-status = c_stat-success.
          ELSE.
            r_state-status = c_stat-warning.
            MESSAGE s124(ztg_zii) INTO r_state-status_text.
          ENDIF.

        ENDIF.
      CATCH cx_root INTO DATA(exref).
        r_state-status = c_stat-error.
        r_state-status_text = exref->get_text( ).
    ENDTRY.

  ENDMETHOD.

Table:

Method Output:

I found CTEs quite practical. CTE simplifies very much the recursive calls or rescues us from writing CDS views while keeping our code readable.

I hope that is useful for you too.

Thanks for reading.