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
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
and save a few lines that way.
thanks Joachim! I tried out your report and found it very helpful to understand these OpenSQL features
Hi David,
thanks for your kind feedback!
Great to learn my blog was helpful to you!
Best
Joachim
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