Hola a todos,

En esta breve publicación de blog, me gustaría compartir algunos ejemplos útiles de CTE.

A veces requerimos consultas SQL complejas y en ese punto tenemos algunas opciones,

Teniendo en cuenta el principio DRY, si lo usaremos en varios lugares, es posible que prefiramos crear cds o amdp según los requisitos. Pero si nuestro requisito es usarlo una vez, entonces podemos usar CTE.

A continuación hay 2 ejemplos de CTE.

Primer ejemplo, 2 consultas con grupo por expresión consumidas en la consulta principal.

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.

Segundo ejemplo, una consulta CTE recursiva para construir un árbol hijo a padre.

Gracias a enes para código de muestra.

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.

Y aquí está el código Abap para la llamada al método recursivo que también crea un árbol hijo a padre.

* <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.

Mesa:

Salida del método:

Los CTE me parecieron bastante prácticos. CTE simplifica mucho las llamadas recursivas o nos rescata de escribir vistas CDS manteniendo nuestro código legible.

Espero que te sea útil también.

Gracias por leer.