Having Clause in Select - Open SQL Enhancement ABAP on HANA - Part 12



*&---------------------------------------------------------------------*
*& Report zr_opensql_20_unioon_grp_by
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zr_opensql_20_having_union.

SELECT
 FROM vbak AS so
 INNER JOIN kna1 AS cust
 ON so~kunnr = cust~kunnr
 FIELDS so~kunnr AS cust_id,
        cust~name1 AS customer,
        so~waerk AS curre,
        SUM( so~netwr ) AS tot_netprice,
        AVG( so~netwr ) AS net,
        MAX( so~netwr ) AS max,
        MIN( so~netwr  ) AS min,
        COUNT( so~netwr ) AS count,
        'Importet CUSSTOMER' AS cat_cust
 GROUP BY so~kunnr,so~waerk,cust~name1
 HAVING SUM( so~netwr ) > 10000
UNION
 SELECT
 FROM vbak AS so
 INNER JOIN kna1 AS cust
 ON so~kunnr = cust~kunnr
 FIELDS so~kunnr AS cust_id,
        cust~name1 AS customer,
        so~waerk AS curre,
        SUM( so~netwr ) AS tot_netprice,
        AVG( so~netwr ) AS net,
        MAX( so~netwr ) AS max,
        MIN( so~netwr  ) AS min,
        COUNT( so~netwr ) AS count,
        'Other CUSSTOMER' AS cat_cust
 GROUP BY so~kunnr,so~waerk,cust~name1
 HAVING SUM( so~netwr ) < 10000
 INTO TABLE @DATA(it_result).
IF sy-subrc IS INITIAL.
  cl_demo_output=>display( it_result ).
ENDIF.


*Select lists of the different selects must
*–have the same number of columns
*–contain compatible types
*Union are use ful where we use appending same data from table using appending table


*************************************************************














* UNION ALL
*
* SELECT
* FROM vbak AS so
* INNER JOIN kna1 AS cust
* ON so~kunnr = cust~kunnr
* FIELDS so~kunnr AS cust_id,
*        cust~name1 AS customer,
*        so~waerk AS curre,
*        SUM( so~netwr ) AS tot_netprice,
*        AVG( so~netwr ) AS net,
*        MAX( so~netwr ) AS max,
*        MIN( so~netwr  ) AS min,
*        COUNT( so~netwr ) AS count,
*        'less Importent' AS category
* GROUP BY so~kunnr,so~waerk,cust~name1
* HAVING  SUM( so~netwr ) < 10000


**********************************************************************
*********

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