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,
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.
Second example, a recursive CTE query to build child to parent tree.
Thanks to Enes for sample code.
And here is the Abap code for recursive method call which also creates child to parent tree.
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.
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.
- SAP Managed Tags:
1 Comment
