CTE( Common Table Expressions) in ABAP SQL -Open SQL Enhancement ABAP o...
*&---------------------------------------------------------------------*
*& Report zr_opensql_26_cte
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zr_opensql_26_cte.
*◾A common table expression creates a temporary tabular result
*set, which can be accessed during execution of the WITH statement.
*Common table expressions can therefore be thought of as temporary views,
*which only exist for the duration of the database access
*CTE create tabular result set via subquery
*which can be used in subsequent queries
*When subtotal require ++ instead of GTT we can use
*Name should start with +
*Optional name list
WITH +aggr( mark,so,cust_id,curre,net_amount,so_count ) AS (
SELECT
FROM vbak
FIELDS 'Total' AS mark,
' ' AS so,
kunnr ,
waerk ,
SUM( netwr ) AS net_amount,
COUNT( netwr ) AS so_count
GROUP BY kunnr,waerk
)
SELECT
FROM vbak
FIELDS ' ' AS mark,
vbeln AS so,
kunnr AS cust_id,
waerk AS curre,
netwr AS net_amount,
1 AS so_count
UNION
SELECT mark,
so,
cust_id,
curre,
net_amount,
so_count
FROM +aggr
ORDER BY cust_id,mark
INTO TABLE @DATA(it_result).
IF sy-subrc IS INITIAL.
cl_demo_output=>display( it_result ).
ENDIF.
*WITH
* +cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] ), ,
* +cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] ),
* ... ]
* SELECT mainquery_clauses
* [UNION ...]
* INTO|APPENDING target
* [UP TO ...] [OFFSET ...]
* [abap_options].
*[ENDWITH]
*zr_opensql_26_cte_1
**********************************************************************
*WITH
* +cities AS (
* SELECT cityfrom AS city
* FROM spfli
* WHERE carrid = 'LH'
* UNION DISTINCT
* SELECT cityto AS city
* FROM spfli
* WHERE carrid = 'LH' )
* SELECT *
* FROM sgeocity
* WHERE city IN ( SELECT city
* FROM +cities )
* INTO TABLE @DATA(result).
*
*cl_demo_output=>display( result ).
***********************************************************************************
*&---------------------------------------------------------------------*
*& Report zr_opensql_26_cte
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zr_opensql_26_cte_1.
CLASS demo DEFINITION.
PUBLIC SECTION.
TYPES: BEGIN OF ls_result,
carrname TYPE scarr-carrname,
connid TYPE spfli-connid,
cityfrom TYPE spfli-cityfrom,
cityto TYPE spfli-cityto,
cnt TYPE int8,
END OF ls_result.
CLASS-DATA it_result TYPE TABLE OF ls_result WITH EMPTY KEY.
CLASS-METHODS main.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD main.
WITH
+conns AS (
SELECT carrname, connid, cityfrom, cityto
FROM spfli
JOIN scarr ON spfli~carrid = scarr~carrid
WHERE spfli~carrid = 'LH' ),
+cnts AS (
SELECT COUNT(*) AS cnt
FROM +conns )
SELECT *
FROM +cnts
CROSS JOIN +conns
ORDER BY carrname, connid
INTO CORRESPONDING FIELDS of TABLE @it_result.
cl_demo_output=>display( it_result ).
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).
**********************************************************************************
*&---------------------------------------------------------------------*
*& Report zr_opensql_26_cte
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zr_opensql_26_cte_2.
CLASS demo DEFINITION.
PUBLIC SECTION.
CLASS-METHODS main.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD main.
DATA carrid TYPE sflight-carrid VALUE 'AA'.
cl_demo_input=>request( CHANGING field = carrid ).
WITH +agg AS (
SELECT carrid,
connid,
CAST( '00000000' AS DATS ) AS fldate,
SUM( seatsocc ) AS seatsocc
FROM sflight
WHERE carrid = @( to_upper( carrid ) )
GROUP BY carrid, connid
)
SELECT ' ' AS mark, carrid, connid, fldate, seatsocc
FROM sflight
WHERE carrid = @( to_upper( carrid ) )
UNION
SELECT 'X' AS mark,
carrid, connid, fldate, seatsocc
FROM +agg
ORDER BY carrid, connid, mark, fldate, seatsocc
INTO TABLE @DATA(it_result).
cl_demo_output=>display( it_result ).
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).
**************************************************************************
Comments
Post a Comment