Enhanced SQL

 REPORT zdemo_esql_02.

" Usage of Sum expression

TYPES: BEGIN OF d_final,

         carrid TYPE s_carr_id,

*         CONNID TYPE S_CONN_ID,

         price  TYPE s_price,

       END OF d_final.

DATA: gt_final TYPE STANDARD TABLE OF d_final.

SELECT sf~carrid,

*       SF~CONNID,

       SUM( sf~price ) AS price

       FROM sflight AS sf

            INNER JOIN sbook AS sb

            ON sf~carrid EQ sb~carrid

            INTO TABLE @gt_final

            GROUP BY sf~carrid

            ORDER BY sf~carrid.

IF sy-subrc EQ 0.

  LOOP AT gt_final ASSIGNING FIELD-SYMBOL(<fs>).

    WRITE:/ <fs>-carrid. "<fs>-connid,<fs>-carrid.

  ENDLOOP.

ENDIF.

REPORT zdemo_esql_03.

" Usage of Case Statement

DATA: lv_fert type mtart.

      lv_fert =  'Finished Products'.

SELECT matnr,

   MTART,

  CASE

    WHEN MTART =  'FERT' THEN @lv_fert

    WHEN MTART  = 'HALB' THEN 'Semifinished Products'"

    ELSE 'Other Material Type'

  END AS desc

  FROM mara

  INTO TABLE @DATA(gt_material) UP TO 10 ROWS.

IF sy-subrc EQ 0.

  LOOP AT gt_material ASSIGNING FIELD-SYMBOL(<fs>).

    WRITE: / <fs>-matnr, 45 <fs>-mtart, 60 <fs>-desc.

  ENDLOOP.

ENDIF.

REPORT zdemo_esql_04.

START-OF-SELECTION.

  PERFORM start_of_selection.

FORM start_of_selection.

*Select

  SELECT

*Using constants

   'Demo_Const' AS demo_of_constant,

     @abap_true AS demo_another_const,

*System fields Sy-fields

  @sy-uname AS current_user,

  @sy-datum AS current_date,

  snwd_bpa~bp_id,

  snwd_bpa~bp_role ,

*case

  CASE snwd_bpa~bp_role

  WHEN '01' THEN 'Customer'

  WHEN '02' THEN 'Suplier'

  ELSE 'un-known'

  END AS bp_role_text,

*coalesce

   coalesce( snwd_bpa~company_name, 'n/a' ) AS company_name,

   snwd_ad~country,

   snwd_ad~city,

   snwd_ad~address_type,

   snwd_so~so_id,

   snwd_so~currency_code,

*coalesce

   coalesce( snwd_so~currency_code, 'n/a' ) AS curr_code_coalesce,

*rounding

   round( snwd_so~gross_amount, 2 ) AS gross_amount,

*case - multi-level

   CASE snwd_so~lifecycle_status

   WHEN 'N' THEN 'new'

   WHEN 'P' THEN

      CASE snwd_so~billing_status

          WHEN 'P' THEN'paid'

          ELSE 'pending'

          END

       ELSE 'undefined'

       END

   AS calculated_status,

*text functions

   snwd_ad~country && '-' &&  snwd_ad~city AS country_city,

*combining sy-fields and concatenate

  @sy-sysid && '/' && @sy-mandt AS sys_info

*[further ideas here!] :-)

   FROM snwd_bpa

   INNER JOIN snwd_ad

   ON snwd_bpa~address_guid = snwd_ad~node_key

   LEFT OUTER JOIN snwd_so ON

   snwd_bpa~node_key = snwd_so~buyer_guid

   INTO TABLE @DATA(lt_data).

*display

  PERFORM display_data CHANGING lt_data.

ENDFORM.

FORM display_data USING pt_data TYPE ANY TABLE.

  DATA: lr_alv          TYPE REF TO cl_salv_table.

  DATA: lr_functions TYPE REF TO cl_salv_functions_list,

        lr_layout    TYPE REF TO cl_salv_layout,

        ls_key       TYPE salv_s_layout_key.

  TRY.

      CALL METHOD cl_salv_table=>factory

        EXPORTING

          list_display = if_salv_c_bool_sap=>false

        IMPORTING

          r_salv_table = lr_alv

        CHANGING

          t_table      = pt_data.

    CATCH cx_salv_msg .

  ENDTRY.

* Layout

  lr_layout = lr_alv->get_layout( ).

  ls_key-report = sy-repid.

  lr_layout->set_key( ls_key ).

  lr_layout->set_default( abap_true ).

  lr_layout->set_save_restriction( if_salv_c_layout=>restrict_none ).

*Functions

  lr_functions = lr_alv->get_functions( ).

  lr_functions->set_all( abap_true ).

  CALL METHOD lr_alv->display.

ENDFORM.                    "display_data

REPORT zdemo_esql_05.

* This example demonstrates one way to use a constant value in the SELECT list.

*Determines whether a row exists for a WHERE condition (regardless of the content of the row).

*A constant is specified to make it unnecessary to specify a column of the database and transport it.

*The target object exists is created using an inline declaration.

CLASS demo DEFINITION.

  PUBLIC SECTION.

    CLASS-METHODS main.

ENDCLASS.

CLASS demo IMPLEMENTATION.

  METHOD main.

    DATA carrier TYPE scarr-carrid.

    cl_demo_input=>request( CHANGING field = carrier ).

    SELECT SINGLE @abap_true

           FROM scarr

           WHERE carrid = @carrier

           INTO @DATA(exists).

      IF exists = abap_true.

        cl_demo_output=>display(

          |Carrier { carrier } exists in SCARR| ).

      ELSE.

        cl_demo_output=>display(

          |Carrier { carrier } does not exist in SCARR| ).

      ENDIF.

    ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

  demo=>main( ).

REPORT zdemo_esql_06.

* To perform floating point division with integer columns,

* these columns are updated to the type FLTP using cast.

* Integer divisions with the functions DIV, MOD, and DIVISION

* do not require this update.

CLASS demo DEFINITION.

  PUBLIC SECTION.

    CLASS-METHODS main.

ENDCLASS.

CLASS demo IMPLEMENTATION.

  METHOD main.

    DATA(rnd) = cl_abap_random_int=>create(

      seed = CONV i( sy-uzeit ) min = 1 max = 100 ).

    DELETE FROM demo_expressions.

    INSERT demo_expressions FROM TABLE @( VALUE #(

      FOR i = 0 UNTIL i > 9

      ( id = |{ i }|

        num1 = rnd->get_next( )

        num2 = rnd->get_next( ) ) ) ).

    DATA(offset) = 10000.

    SELECT id, num1, num2,

           CAST( num1 AS FLTP ) / CAST( num2 AS FLTP ) AS ratio,

           div( num1, num2 ) AS div,

           division( num1, num2, 2 ) AS division,

           mod( num1, num2 ) AS mod,

           @offset + abs( num1 - num2 ) AS sum

           FROM demo_expressions

           ORDER BY sum DESCENDING

           INTO TABLE @DATA(results).

    cl_demo_output=>display( results ).

  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

  demo=>main( ).

REPORT zdemo_esql_07.

* Strings can also be concatenated using the SQL function CONCAT.

* The function CONCAT is restricted to concatenations of two operands

* but has the following benefits when compared to the operator &&:

* It allows expressions to be used as operands.

* It allows more data types.

* It does not apply any special handling to constants and text field literals

* that contain only blanks.

* It allows results to be longer than 255 characters.

* The operator && can only be used to chain the elementary operands stated above. Any other expressions cannot be chained.

*  The result of the chaining must be no longer than 255 characters.

CLASS demo DEFINITION.

  PUBLIC SECTION.

    CLASS-METHODS main.

ENDCLASS.

CLASS demo IMPLEMENTATION.

  METHOD main.

    DELETE FROM demo_expressions.

    INSERT demo_expressions FROM TABLE @( VALUE #(

      ( id = 'x' char1 = 'aaaaa' char2 = 'bbbbb' )

      ( id = 'y' char1 = 'xxxxx' char2 = 'yyyyy' )

      ( id = 'z' char1 = 'mmmmm' char2 = 'nnnnn' ) ) ).

    DATA(else) = 'fffff'.

    SELECT id, char1, char2,

           CASE char1

             WHEN 'aaaaa' THEN ( char1 && char2 )

             WHEN 'xxxxx' THEN ( char2 && char1 )

             ELSE @else

           END AS text

           FROM demo_expressions

           INTO TABLE @DATA(results).

    cl_demo_output=>display( results ).

  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

  demo=>main( ).

REPORT zdemo_esql_08.

* The example shows two SELECT statements that differ only in the arrangement

* of their SELECT and FROM clauses.

* The result of the two statements,

* which access two database tables via an INNER JOIN, is identical.

DATA: cityfrom       TYPE spfli-cityfrom VALUE 'FRANKFURT',

      lcl_ex_handler TYPE REF TO cx_root.

SELECT c~carrname,

       p~connid,

       p~cityfrom,

       p~cityto

       FROM scarr AS c

            INNER JOIN spfli AS p

                  ON c~carrid = p~carrid

       WHERE p~cityfrom = @cityfrom

       ORDER BY c~carrname, p~connid, p~cityfrom, p~cityto

       INTO TABLE @DATA(result1)

       UP TO 10 ROWS.

* Alternate syntax from

SELECT FROM scarr AS c

            INNER JOIN spfli AS p

                  ON c~carrid = p~carrid

       FIELDS c~carrname, p~connid, p~cityfrom, p~cityto

       WHERE p~cityfrom = @cityfrom

       ORDER BY c~carrname, p~connid, p~cityfrom, p~cityto

       INTO TABLE @DATA(result2)

       UP TO 10 ROWS.

ASSERT result2 = result1.

cl_demo_output=>display( result1 ).

REPORT zdemo_esql_09.

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

    SELECT 'I' AS mark, carrid, connid, fldate, seatsocc

           FROM sflight

           WHERE carrid = @( to_upper( carrid ) )

           UNION

           SELECT 'T' AS mark,

                        carrid,

                        connid,

                        "@( CONV d( '00000000' ) ) AS fldate,

                        CAST( '00000000' AS DATS ) AS fldate,

                        SUM( seatsocc ) AS seatsocc

                        FROM sflight

                        WHERE carrid = @( to_upper( carrid ) )

                        GROUP BY carrid, connid

           ORDER BY carrid, connid, mark, fldate, seatsocc

           INTO TABLE @DATA(result).

   cl_demo_output=>display( result ).

  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

  demo=>main( ).

REPORT zdemo_esql_10.

  SELECT 'Item' as Mark,

         a~carrid,

         a~connid,

         b~fldate,

  CASE a~custtype

  WHEN 'B' THEN 'Business Partner'

  WHEN 'P' THEN 'Individual'

  END AS custtype,

a~forcuram * 10 as multiplied_curr,

  'From' && ' ' && c~cityfrom && ' ' &&

  'To'   && ' ' && c~cityto AS dest,

  coalesce( d~carrname, 'N/A' ) AS pname

  FROM sbook AS a

  INNER JOIN sflight as b  on  a~carrid EQ b~carrid

                           and a~connid EQ b~connid

                           and a~fldate EQ b~fldate

  LEFT OUTER JOIN spfli AS c

  ON a~carrid EQ c~carrid

  LEFT OUTER JOIN scarr AS d

  ON a~carrid EQ d~carrid

  UNION SELECT 'Totl' as Mark,

         a~carrid,

         a~connid,

         CAST( '00000000' AS DATS ) AS fldate,

  CASE a~custtype

  WHEN 'B' THEN 'Business Partner'

  WHEN 'P' THEN 'Individual'

  END AS custtype,

  SUM( a~forcuram * 10 ) AS multiplied_curr,

  @space AS dest,

  @space AS pname

  FROM sbook AS a

  INNER JOIN sflight as b  on  a~carrid EQ b~carrid

                           and a~connid EQ b~connid

                           and a~fldate EQ b~fldate

  LEFT OUTER JOIN spfli AS c

  ON a~carrid EQ c~carrid

  LEFT OUTER JOIN scarr AS d

  ON a~carrid EQ d~carrid

 GROUP BY a~carrid,

            a~connid,

            a~custtype

 ORDER BY  carrid,

           connid,

           custtype,

           mark,

           fldate

             INTO TABLE @DATA(gt_records).

  TRY.

      cl_salv_table=>factory(

         IMPORTING

           r_salv_table   = DATA(lo_alv_display)

        CHANGING

          t_table        =  gt_records ).

    CATCH cx_salv_msg INTO DATA(lref_msg). " ALV: General Error Class with Message

      DATA(lv_error) = lref_msg->get_message( ).

  ENDTRY.

  lo_alv_display->display( ).