Skip to Content
Author's profile photo Joachim Rees

Exploring SQL-Features using a demo report

Not sure if it’s helpful for others, but I think it’s for me and I’ll gladly share it with you here:

 

The combination of dynamically creating a target table for a select (using inline declaration) and the capability of cl_salv_table to take any table and just display it, makes it very easy to play around with select statements (e.g. exploring advanced OpenSQL-Features) and getting your results displayed in a report with very little overhead.

 

Those are the key statements:

Select ....

 into table @data(lt_data).

 CALL METHOD cl_salv_table=>factory

 

That’s the main idea of this blog: Create a little report once, then play around with the select, immediately seeing if and how things work.
And here’s my full report:

 

REPORT ZZ_SQL_play_report.


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,

*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, 1 ) 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

 

Copy and paste and try it out for yourself, if you like!

 

 

(Notes:

– I created this on an NW7.51 System

– I think the SNWD*-Tables should be part of a default installation – if not, you can easily replace the select with tables fitting more to your daylie work.

)

 

How do you take on exploring new ABAP Features?

Joachim

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Joachim Rees
      Joachim Rees
      Blog Post Author

      Note: I like having a full featured ALV using form display_data, but to only display the result, one of course could also just use

       CL_DEMO_OUTPUT=>DISPLAY( lt_data ).

      and save a few lines that way.

      Author's profile photo David Tuohy
      David Tuohy

      thanks Joachim! I tried out your report and found it very helpful to understand these OpenSQL features

      Author's profile photo Joachim Rees
      Joachim Rees
      Blog Post Author

      Hi David,

      thanks for your kind feedback!
      Great to learn my blog was helpful to you!

      Best
      Joachim

      Author's profile photo Joachim Rees
      Joachim Rees
      Blog Post Author

      By the way:

      The select also works perfectly in the SQL-Console of AdT / Eclipse:

      [sorry, it seems I currently can't upload a screenshot! 🙁 ]

       

       

      Another way to quickly try out new things™ !

      Joachim