Window Expressions in ABAP SQL -Open SQL Enhancement ABAP on HANA - Part 14



*&---------------------------------------------------------------------*
*& Report zr_opensql_23_widow_expression
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zr_opensql_23_widow_expression.

SELECT
 FROM vbak
 FIELDS vbeln AS so,
        kunnr AS cust_id,
        waerk AS curre,
        netwr AS net_amt,
        SUM( netwr ) OVER(
                         PARTITION BY kunnr
                         ORDER BY netwr
*ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
* ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
                         ) AS net_amount_per_cust,
        COUNT( netwr ) OVER(
                        PARTITION BY kunnr
                        ORDER BY netwr
*ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
* ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
                         ) AS so_count_per_cudt
 WHERE kunnr IN ( '0000000001','0000000111' )
 INTO TABLE @DATA(it_result).
IF sy-subrc IS INITIAL.
  cl_demo_output=>display( it_result ).
ENDIF.
**********************************************************************

*Over Clause :- It define partitioning and ordering of rows
*  where window function can be perform
*Order BY Clause :- Define the order of the rows and frame  within current window
*Partition by clause :- divide query result in partition.
*  Then window Function apply to each partition
*  If we don' define it take entire result
*Rows and range Clause :- It limits the rows within partition.
*  By specifying start and end point within partition
* ORDER BY is mandatory.
*ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
*ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
*ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
*Window Function
*  | AVG( col [AS dtype] )
*  | MEDIAN( sql_exp )
*  | MAX( sql_exp )
*  | MIN( sql_exp )
*  | SUM( sql_exp )
*  | STDDEV( sql_exp )
*  | VAR( sql_exp )
*  | CORR( sql_exp1,sql_exp2 )
*  | CORR_SPEARMAN( sql_exp,sql_exp2 )
*  | COUNT( sql_exp )
*  | COUNT( * )
*  | COUNT(*)
*  | ROW_NUMBER( )
*  | RANK( )
*  | DENSE_RANK( )
*  | NTILE( n )
*  | LEAD|LAG( sql_exp1[, diff[, sql_exp2]] )
*  | FIRST_VALUE|LAST_VALUE( col ) ...


*
*        AVG( netwr ) OVER(  PARTITION BY kunnr
*                           ORDER BY kunnr,netwr
*                           rows BETWEEN UNBOUNDED  PRECEDING AND  UNBOUNDED FOLLOWING
*                           ) AS avg_cust_net,
*        COUNT( netwr ) OVER( PARTITION BY kunnr
*                             ORDER BY kunnr,netwr
*                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
*                             ) AS avg_cust_count
**********************************************************************

Comments

Post a Comment

Popular posts from this blog

AMDP ( ABAP Managed Database Procedure ) Part - 1

Image
CLASS zcl_amdp_class DEFINITION PUBLIC FINAL CREATE PUBLIC . PUBLIC SECTION. INTERFACES if_amdp_marker_hdb . TYPES: BEGIN OF ty_cust , cust_name TYPE kna1 - name1 , netwr TYPE vbak - netwr , END OF ty_cust , tt_cust TYPE TABLE OF ty_cust . TYPES: BEGIN OF session_variables , client TYPE sy - mandt , cds_client TYPE sy - mandt , uname TYPE sy - uname , langu TYPE sy - langu , datum TYPE sy - datum , END OF session_variables . CLASS-METHODS get_session_variables_amdp AMDP OPTIONS READ-ONLY CDS SESSION CLIENT iv_clnt IMPORTING VALUE( iv_clnt ) TYPE sy - mandt EXPORTING VALUE( clnt ) TYPE session_variables - client VALUE( cds_clnt ) TYPE session_variables - cds_client VALUE( unam ) TYPE session_variables - uname VALUE( lang ) TYPE session_variables - langu ...
Read more

Backup all ADT Objects & Other queries RAP Part 10.1

CLASS zcl_DATA_GENERATOR DEFINITION PUBLIC FINAL CREATE PUBLIC . PUBLIC SECTION. INTERFACES: if_oo_adt_classrun . PROTECTED SECTION. PRIVATE SECTION. ENDCLASS . CLASS zcl_DATA_GENERATOR IMPLEMENTATION. METHOD if_oo_adt_classrun ~ main . " delete existing entries in the database table DELETE FROM ztravel_tech_m . DELETE FROM zBOOKING_tech_m . DELETE FROM zbooksupp_tech_m . COMMIT WORK. " insert travel demo data INSERT ztravel_tech_m FROM ( SELECT * FROM /dmo/travel_m ). COMMIT WORK. " insert booking demo data INSERT zbooking_tech_m FROM ( SELECT * FROM /dmo/booking_m * JOIN ztravel_tech_m AS z * ON booking~travel_id = z~travel_id ). COMMIT WORK. INSERT zbooksupp_tech_m FROM ( SELECT * FROM /dmo/booksuppl_m * JOIN ztravel_tech_m AS z * ON booking~travel_id = z~travel_id ). ...
Read more

SAP CDS Introduction Part 2 - ABAP on HANA Course

Image
Code :- @AbapCatalog.sqlViewName: 'ZVSQL_SAMPLE_01' @EndUserText.label: 'This is my fist DDIC based CDS' @AbapCatalog.preserveKey: true @AbapCatalog: {     buffering: {         status:#SWITCHED_OFF,         type: #NONE,         numberOfKeyFields: 000     },     viewEnhancementCategory: [#PROJECTION_LIST,#UNION],     compiler: {         compareFilter: true     },     dataMaintenance: #RESTRICTED } @AccessControl.authorizationCheck: #CHECK @ClientHandling: {     type: #INHERITED,     algorithm:#SESSION_VARIABLE } define view zcds_sample_01 --( SO, Kunnr, curr,netwr,clnt,sydate,syslang)   as select from vbak   association [1..*] to vbap as _item on vbak.vbeln = _item.vbeln {   key   vbeln                    as so,         kunnr  ...
Read more