SDB_ADBC - the program

[rosenberg_eitan]

Hi all,

As I wrote here http://scn.sap.com/community/abap/blog/2013/12/23/sdbadbc--power-to-the-people

I have been playing with package SDB_ADBC and its gang members....

Based on what I learned from program ADBC_QUERY I wrote a program (Y_R_EITAN_TEST_28_01) that I hope you will find it useful .

The program allow the user (A programmer) to query data using the raw native power of the local database.

For example In case of Oracle you can refer to Oracle documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm).

- The program create SQL statment based on the selected fields and functions (FORM get_statment) .
- The program create a dynamic table based on the selected fields and functions (FORM run_statment) .
- Using SDB_ADBC clases the program query the data and fill the dynamic table (FORM run_statment).
- The table is presented using CL_SALV_TABLE .

The native SQL is not client dependent so using MANDT in the where clue is required.

Limitation:
  The program is not design for joins (This require more development and I do not have the time...)
 
Some screens:

Source:

Y_R_EITAN_TEST_28_01.txt - the program.

Y_R_EITAN_TEST_28_01_screen_100.txt - screen source .(it is very simple since I use OO components )

STATUS_COMMON

Define BACK , EXIT and CANCEL as Exit Command.

TITLE_COMMON

That is all for now .

Have fun.

Update 2013/12/29

I added multi function support.(More power to the peoples...)

See screen cap:

Update 2014/01/01

Since I hate typing I just added the SUM function as a check box.

Also I forgot to mention that a click on the field name will append the field name to the text box (As I said I hate typing... )

Update: 19/02/2014

Take into account those pesky "name space" fields.

[former_member192854]

  CALL METHOD cl_alv_table_create=>create_dynamic_table

    EXPORTING

      it_fieldcatalog = it_fieldcatalog

    IMPORTING

      ep_table        = g_data-it_data.

:sad: , what about RTTI?

[rosenberg_eitan]

Hi,

True .

I wanted to use RTTI but I wanted to INCREASE the size of field when using SUM function
and at the same time I wanted to retain the headings .

Regards.

I even asked :

I want to use RTTC but.....

[former_member192854]

Considered CL_SALV_TABLE?

* Short sample

DATA lt_u TYPE STANDARD TABLE OF usr02.

SELECT *

   FROM usr02

   INTO TABLE lt_u.

cl_salv_table=>factory(

IMPORTING r_salv_table = DATA(lo_alv)

CHANGING t_table = lt_u ).

lo_alv->get_columns( )->set_optimize( ).

lo_alv->display( ).

[rosenberg_eitan]

Hi,

I do not understand what you mean ?

The program is using CL_SALV_TABLE to dispaly the output .

For field selection I use cl_gui_alv_grid (input fields) .

Regards.

[MariusStoica]

Hi Etian,

I know you are retired. Is there a chance that you could upload the program? There is no download link. Or maybe any other fellow ABAPer could upload it.

Kind regards,

Marius

PS: Enjoy your retirement :).

[rosenberg_eitan]

A little bit late but this is what I manage to salvage:

Screen

****************************************************************
* This file was generated by Direct Download Enterprise. *
* Please do not change it manually. *
****************************************************************
%_DYNPRO
Y_R_EITAN_TEST_28_01
0200
701
40
%_HEADER
Y_R_EITAN_TEST_28_01 0200 0200 0 0192 37 0 0 2 14 0G E 20171015115030
%_DESCRIPTION
SQL input
%_FIELDS
OK_CODE CHAR 20 80 10 00 00 00 255 1 O 0 0 0 0 0 0 ____________________
%_FLOWLOGIC

PROCESS BEFORE OUTPUT.
MODULE at_pbo .
*
PROCESS AFTER INPUT.
MODULE at_pai_exit AT EXIT-COMMAND.
MODULE at_pai .

Code:

REPORT y_r_eitan_test_28_01 .

* See ADBC_QUERY

TYPE-POOLS: abap, cntb, icon, adbc.

CONSTANTS: c_counter TYPE fieldname VALUE 'CCCCC_01' .
CONSTANTS: c_select_a TYPE syucomm VALUE 'SELECT_ALL ' .
CONSTANTS: c_select_n TYPE syucomm VALUE 'SELECT_NONE' .
CONSTANTS: c_fun_count TYPE syucomm VALUE 'COUNT' .
CONSTANTS: c_fun_sum TYPE syucomm VALUE 'SUM' .
CONSTANTS: c_fun_avg TYPE syucomm VALUE 'AVG' .

*----------------------------------------------------------------------*
CONSTANTS: c_styl_fname TYPE lvc_ctfnm VALUE 'IT_STYL' .
*----------------------------------------------------------------------*
TYPES: BEGIN OF tp_function .
TYPES: function TYPE char128 .
TYPES: END OF tp_function .
*----------------------------------------------------------------------*

TYPES: BEGIN OF tp_fields_used .

TYPES: it_styl TYPE lvc_t_styl .

TYPES: use_field TYPE checkbox ,
tempname TYPE fieldname .

TYPES: use_sum TYPE checkbox ,
functions TYPE char128 .

INCLUDE TYPE lvc_s_fcat AS fcat RENAMING WITH SUFFIX _fcat .

TYPES: END OF tp_fields_used .

TYPES: tp_fields_used_tab TYPE TABLE OF tp_fields_used .

*----------------------------------------------------------------------*

DATA: ok_code TYPE syucomm ,
ok_save TYPE syucomm .

CONSTANTS: c_use_field_columns TYPE char32 VALUE '<use_field columns>' .

DATA: ob_gui_textedit_sql TYPE REF TO cl_gui_textedit .
DATA: ob_gui_textedit_err TYPE REF TO cl_gui_textedit .
DATA: ob_gui_alv_grid_fld TYPE REF TO cl_gui_alv_grid .
DATA: ob_salv_table_out TYPE REF TO cl_salv_table .

CLASS: cl_event_receiver DEFINITION DEFERRED.

DATA: ob_event_receiver TYPE REF TO cl_event_receiver .

DATA: BEGIN OF g_data .
DATA: it_fields_used TYPE TABLE OF tp_fields_used .
DATA: it_data TYPE REF TO data .
DATA: END OF g_data .

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_my_string DEFINITION .

PUBLIC SECTION .

CLASS-METHODS: get_quoted
IMPORTING value(inp) TYPE any
RETURNING value(out) TYPE string .

ENDCLASS . "cl_my_string DEFINITION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_my_string IMPLEMENTATION.

METHOD: get_quoted .

IF inp CS '/' .
CONCATENATE '"' inp '"' INTO out.
ELSE .
CONCATENATE '' inp '' INTO out.
ENDIF .

ENDMETHOD . "get_quoted

ENDCLASS . "cl_my_string IMPLEMENTATION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_event_receiver DEFINITION .

PUBLIC SECTION.

METHODS: toolbar
FOR EVENT toolbar OF cl_gui_alv_grid
IMPORTING e_object e_interactive .

METHODS: user_command
FOR EVENT user_command OF cl_gui_alv_grid
IMPORTING e_ucomm .

METHODS: hotspot_click
FOR EVENT hotspot_click OF cl_gui_alv_grid
IMPORTING e_row_id e_column_id es_row_no .

METHODS: double_click
FOR EVENT double_click OF cl_salv_events_table
IMPORTING row column .

PRIVATE SECTION .

METHODS: append_value
IMPORTING my_value TYPE string .

ENDCLASS . "cl_event_receiver DEFINITION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
CLASS cl_event_receiver IMPLEMENTATION.

*----------------------------------------------------------------------*
METHOD: toolbar .

DATA: st_button TYPE stb_button .
FIELD-SYMBOLS: <st_toolbar> LIKE LINE OF e_object->mt_toolbar .

DATA: it_toolbar TYPE ttb_button .

* Kill grid tools functions(e.g. add,copy etc.)
LOOP AT e_object->mt_toolbar ASSIGNING <st_toolbar> .

IF <st_toolbar>-function CS 'LOCAL' .
DELETE e_object->mt_toolbar .
CONTINUE .
ENDIF .

CASE <st_toolbar>-function .
WHEN cl_gui_alv_grid=>mc_fc_sort_asc OR cl_gui_alv_grid=>mc_fc_sort_dsc OR
cl_gui_alv_grid=>mc_fc_sum OR cl_gui_alv_grid=>mc_mb_sum OR
cl_gui_alv_grid=>mc_fc_print OR cl_gui_alv_grid=>mc_fc_refresh OR
cl_gui_alv_grid=>mc_fc_detail OR cl_gui_alv_grid=>mc_fc_current_variant OR
cl_gui_alv_grid=>mc_fc_graph OR cl_gui_alv_grid=>mc_fc_info OR
cl_gui_alv_grid=>mc_mb_subtot OR cl_gui_alv_grid=>mc_fc_print_back.

DELETE e_object->mt_toolbar .
CONTINUE .

ENDCASE .

APPEND <st_toolbar> TO it_toolbar .

ENDLOOP.

* Append a separator
CLEAR st_button.
st_button-butn_type = cntb_btype_sep .
APPEND st_button TO e_object->mt_toolbar.

CLEAR st_button .
st_button-function = c_select_a .
st_button-icon = icon_select_all .
APPEND st_button TO e_object->mt_toolbar.

CLEAR st_button .
st_button-function = c_select_n .
st_button-icon = icon_deselect_all .
APPEND st_button TO e_object->mt_toolbar.

ENDMETHOD . "toolbar
*----------------------------------------------------------------------*
METHOD user_command .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF g_data-it_fields_used .

CASE e_ucomm.
WHEN c_select_a .
LOOP AT g_data-it_fields_used ASSIGNING <st_fields_used> .

<st_fields_used>-use_field = abap_true .

IF <st_fields_used>-functions CS c_fun_count .
<st_fields_used>-use_field = abap_false .
ENDIF .

ENDLOOP .
WHEN c_select_n .
LOOP AT g_data-it_fields_used ASSIGNING <st_fields_used> .
<st_fields_used>-use_field = abap_false .
ENDLOOP .
WHEN OTHERS.
ENDCASE.

CALL METHOD ob_gui_alv_grid_fld->refresh_table_display.

ENDMETHOD . "handle_user_command
*----------------------------------------------------------------------*
METHOD hotspot_click .

CHECK es_row_no-row_id GT 0 .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF g_data-it_fields_used .
FIELD-SYMBOLS: <value> TYPE ANY .

READ TABLE g_data-it_fields_used INDEX es_row_no-row_id ASSIGNING <st_fields_used> .

ASSIGN COMPONENT e_column_id-fieldname OF STRUCTURE <st_fields_used> TO <value> .

DATA: my_value TYPE string .

my_value = cl_my_string=>get_quoted( inp = <value> ) .

me->append_value( EXPORTING my_value = my_value ) .

ENDMETHOD . "hotspot_click
*----------------------------------------------------------------------*
METHOD: double_click .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF g_data-it_fields_used .

READ TABLE g_data-it_fields_used ASSIGNING <st_fields_used>
WITH KEY
tempname = column .

CHECK sy-subrc EQ 0 .

DATA: my_value TYPE string .

me->append_value( EXPORTING my_value = 'and' ) .

my_value = cl_my_string=>get_quoted( inp = <st_fields_used>-fcat-fieldname ) .

me->append_value( EXPORTING my_value = my_value ) .

FIELD-SYMBOLS: <it_data> TYPE STANDARD TABLE .
FIELD-SYMBOLS: <st_data> TYPE ANY .

ASSIGN g_data-it_data->* TO <it_data> .

DATA: it_str_dref TYPE REF TO data .

CREATE DATA it_str_dref LIKE LINE OF <it_data> .
ASSIGN it_str_dref->* TO <st_data> .

FIELD-SYMBOLS: <value> TYPE ANY .

READ TABLE <it_data> INDEX row ASSIGNING <st_data> .

ASSIGN COMPONENT column OF STRUCTURE <st_data> TO <value> .

me->append_value( EXPORTING my_value = '=' ) .

my_value = <value> .

CASE <st_fields_used>-fcat-inttype .
WHEN 'C' .
CONCATENATE '''' my_value '''' INTO my_value .
WHEN OTHERS.
ENDCASE.

me->append_value( EXPORTING my_value = my_value ) .

ENDMETHOD . "double_click_salv
*----------------------------------------------------------------------*
METHOD: append_value .

DATA: it_text TYPE soli_tab.

CALL METHOD ob_gui_textedit_sql->get_text_as_stream
IMPORTING
text = it_text
EXCEPTIONS
OTHERS = 1.

DATA: my_string TYPE string .

CALL METHOD cl_bcs_convert=>txt_to_string
EXPORTING
it_soli = it_text
RECEIVING
ev_string = my_string.

CONCATENATE my_string my_value INTO my_string SEPARATED BY space .

CALL METHOD cl_bcs_convert=>string_to_soli
EXPORTING
iv_string = my_string
RECEIVING
et_soli = it_text.

CALL METHOD ob_gui_textedit_sql->set_text_as_stream
EXPORTING
text = it_text
EXCEPTIONS
error_dp = 1
error_dp_create = 2
OTHERS = 3.

ENDMETHOD . "append_value
*----------------------------------------------------------------------*

ENDCLASS . "cl_event_receiver IMPLEMENTATION
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK block04 WITH FRAME .
PARAMETERS: p_tabnam TYPE dd02l-tabname OBLIGATORY DEFAULT 'SBOOK' .
SELECTION-SCREEN END OF BLOCK block04 .
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
AT SELECTION-SCREEN .
PERFORM at_selection_screen_input .

START-OF-SELECTION.
PERFORM at_start_of_selection .
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM at_selection_screen_input .

CHECK sy-ucomm EQ 'ONLI' .

PERFORM get_data_1 .

ENDFORM . "at_selection_screen_input
*----------------------------------------------------------------------*
FORM at_start_of_selection .
CALL SCREEN 200 .
ENDFORM. "at_start_of_selection
*----------------------------------------------------------------------*
FORM get_data_1 .

DATA: st_dd02l TYPE dd02l .

SELECT SINGLE * INTO st_dd02l
FROM dd02l
WHERE
tabname EQ p_tabnam .

IF sy-subrc NE 0 .
MESSAGE e007(e2) WITH p_tabnam .
ENDIF .

DATA: st_tddat TYPE tddat .

SELECT SINGLE * INTO st_tddat
FROM tddat
WHERE
tabname EQ p_tabnam .

IF sy-sysid EQ 'PRD' .

* Protect sensitive files from
AUTHORITY-CHECK OBJECT 'S_TABU_DIS'
ID 'DICBERCLS' FIELD st_tddat-cclass
ID 'ACTVT' FIELD '03' .

IF sy-subrc NE 0 .
MESSAGE e419(mo) .
ENDIF .

ENDIF .

CLEAR g_data .

DATA: it_fieldcatalog TYPE lvc_t_fcat .

CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
EXPORTING
i_structure_name = p_tabnam
CHANGING
ct_fieldcat = it_fieldcatalog
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.

IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

IF it_fieldcatalog[] IS INITIAL .
MESSAGE e007(e2) WITH p_tabnam .
ENDIF .

DATA: st_fieldcatalog LIKE LINE OF it_fieldcatalog .

* Define field for counter .
st_fieldcatalog-fieldname = c_counter .
st_fieldcatalog-ref_table = 'TAB_ORA' .
st_fieldcatalog-ref_field = 'NUM_ROWS' .

INSERT st_fieldcatalog INTO it_fieldcatalog INDEX 1 .

* Add details to field catalogue
CALL FUNCTION 'LVC_FIELDCAT_COMPLETE'
CHANGING
ct_fieldcat = it_fieldcatalog.

DATA: st_fields_used LIKE LINE OF g_data-it_fields_used .

FIELD-SYMBOLS: <st_fieldcatalog> LIKE LINE OF it_fieldcatalog .

DATA: st_styl LIKE LINE OF st_fields_used-it_styl .

LOOP AT it_fieldcatalog ASSIGNING <st_fieldcatalog> .

CLEAR st_fields_used .

st_fields_used-use_field = abap_true .

CASE <st_fieldcatalog>-datatype .
WHEN 'INT2' OR 'INT4' OR 'INT1' OR
'DEC' OR 'QUAN' OR 'CURR' .

WHEN OTHERS .

st_styl-fieldname = 'USE_SUM' .
st_styl-style = cl_gui_alv_grid=>mc_style_disabled .
INSERT st_styl INTO TABLE st_fields_used-it_styl .

ENDCASE .

CASE <st_fieldcatalog>-fieldname .

WHEN c_counter .

st_styl-fieldname = 'FUNCTIONS' .
st_styl-style = cl_gui_alv_grid=>mc_style_disabled .
INSERT st_styl INTO TABLE st_fields_used-it_styl .

st_styl-fieldname = 'USE_SUM' .
st_styl-style = cl_gui_alv_grid=>mc_style_disabled .
INSERT st_styl INTO TABLE st_fields_used-it_styl .

st_fields_used-use_field = abap_false .

st_fields_used-functions = c_fun_count .

WHEN OTHERS.
ENDCASE.

st_fields_used-fcat = <st_fieldcatalog> .
APPEND st_fields_used TO g_data-it_fields_used .

ENDLOOP .

ENDFORM . "get_data_1
*----------------------------------------------------------------------*
MODULE at_pbo OUTPUT.
PERFORM at_pbo .
ENDMODULE. "at_pbo OUTPUT
*----------------------------------------------------------------------*
MODULE at_pai_exit INPUT.
LEAVE TO SCREEN 0 .
ENDMODULE. "at_pai_exit INPUT
*----------------------------------------------------------------------*
MODULE at_pai INPUT.
CASE syst-dynnr.
WHEN 0200 .
PERFORM at_pai_200 .
ENDCASE.
ENDMODULE. "at_pai INPUT
*----------------------------------------------------------------------*
FORM at_pbo .

DATA: my_title TYPE sytitle .

my_title = sy-title .

SET TITLEBAR 'TITLE_COMMON' WITH my_title .

SET PF-STATUS 'STATUS_COMMON' .

CASE syst-dynnr.
WHEN 0200 .
PERFORM at_pbo_200 .
ENDCASE.

ENDFORM . " AT_PBO
*----------------------------------------------------------------------*
FORM at_pbo_200 .

CHECK ob_event_receiver IS INITIAL .

DATA: ob_gui_splitter_container_1 TYPE REF TO cl_gui_splitter_container .
DATA: ob_gui_splitter_container_2 TYPE REF TO cl_gui_splitter_container .
DATA: ob_gui_container_21 TYPE REF TO cl_gui_container.
DATA: ob_gui_container_22 TYPE REF TO cl_gui_container.
DATA: ob_gui_container_23 TYPE REF TO cl_gui_container.

CREATE OBJECT ob_event_receiver .

CREATE OBJECT ob_gui_splitter_container_1
EXPORTING
parent = cl_gui_container=>screen0
rows = 2
columns = 1.

DATA: ob_gui_container_01 TYPE REF TO cl_gui_container.

CALL METHOD ob_gui_splitter_container_1->get_container
EXPORTING
row = 1
column = 1
RECEIVING
container = ob_gui_container_01.

CREATE OBJECT ob_gui_splitter_container_2
EXPORTING
parent = ob_gui_container_01
rows = 1
columns = 3.

CALL METHOD ob_gui_splitter_container_2->set_column_width
EXPORTING
id = 1
width = 30.

CALL METHOD ob_gui_splitter_container_2->set_column_width
EXPORTING
id = 2
width = 30.

CALL METHOD ob_gui_splitter_container_2->get_container
EXPORTING
row = 1
column = 1
RECEIVING
container = ob_gui_container_21.

CALL METHOD ob_gui_splitter_container_2->get_container
EXPORTING
row = 1
column = 2
RECEIVING
container = ob_gui_container_22.

CALL METHOD ob_gui_splitter_container_2->get_container
EXPORTING
row = 1
column = 3
RECEIVING
container = ob_gui_container_23.

DATA: ob_gui_container_02 TYPE REF TO cl_gui_container.

CALL METHOD ob_gui_splitter_container_1->get_container
EXPORTING
row = 2
column = 1
RECEIVING
container = ob_gui_container_02.

CREATE OBJECT ob_gui_textedit_sql
EXPORTING
parent = ob_gui_container_21
wordwrap_mode = cl_gui_textedit=>wordwrap_at_windowborder.

CREATE OBJECT ob_gui_textedit_err
EXPORTING
parent = ob_gui_container_22
wordwrap_mode = cl_gui_textedit=>wordwrap_at_windowborder.

PERFORM set_alv_table_fld
CHANGING
ob_gui_container_23 .

ob_gui_textedit_err->set_readonly_mode( ) .

PERFORM set_alv_table_out
CHANGING
ob_gui_container_02 .

STATICS: it_text TYPE soli_tab.

DATA: st_text LIKE LINE OF it_text.

DATA: tabnam TYPE string .

tabnam = cl_my_string=>get_quoted( inp = p_tabnam ) .

IF it_text[] IS INITIAL .

CONCATENATE 'select' c_use_field_columns 'from' tabnam INTO st_text SEPARATED BY space .

APPEND st_text TO it_text .
APPEND 'where' TO it_text .

DATA: st_fields_used LIKE LINE OF g_data-it_fields_used .

* Is the table client dependent .
READ TABLE g_data-it_fields_used INTO st_fields_used
WITH KEY
fcat-datatype = 'CLNT' .

IF sy-subrc EQ 0 .
CONCATENATE st_fields_used-fcat-fieldname '=' sy-mandt 'and' INTO st_text SEPARATED BY space .
APPEND st_text TO it_text .
ENDIF .

CONCATENATE 'rownum <=' '100' INTO st_text SEPARATED BY space .
APPEND st_text TO it_text .

CALL METHOD ob_gui_textedit_sql->set_text_as_r3table
EXPORTING
table = it_text
EXCEPTIONS
error_dp = 1
error_dp_create = 2
OTHERS = 3.

ENDIF .

ENDFORM . "at_pbo_200
*----------------------------------------------------------------------*
FORM at_pai_200 .

CHECK sy-ucomm EQ 'RUN_SQL' .

ob_gui_textedit_err->delete_text( ) .

DATA: it_fields_used TYPE tp_fields_used_tab .

PERFORM get_fields_used
CHANGING
it_fields_used.

DATA: sql_statment TYPE string .

PERFORM get_statment
USING
it_fields_used
CHANGING
sql_statment .

PERFORM run_statment
USING
sql_statment
CHANGING
it_fields_used .

ENDFORM . "AT_PAI_200
*----------------------------------------------------------------------*
FORM get_fields_used
CHANGING
it_fields_used TYPE tp_fields_used_tab .

DATA: st_fields_used LIKE LINE OF it_fields_used .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF it_fields_used .

DATA: it_functions TYPE TABLE OF tp_function .
DATA: st_functions LIKE LINE OF it_functions .
FIELD-SYMBOLS: <st_functions> LIKE LINE OF it_functions .

* Get just the telected fields
LOOP AT g_data-it_fields_used ASSIGNING <st_fields_used>
WHERE
use_field EQ abap_true .

CLEAR it_functions .

SPLIT <st_fields_used>-functions AT space INTO TABLE it_functions .

IF it_functions[] IS NOT INITIAL .
LOOP AT it_functions ASSIGNING <st_functions> .
IF <st_functions>-function IS INITIAL .
DELETE it_functions .
ENDIF .
ENDLOOP .
ENDIF .

IF <st_fields_used>-use_sum EQ abap_true .
st_functions-function = 'SUM' .
COLLECT st_functions INTO it_functions .
ENDIF .

IF it_functions[] IS INITIAL .
APPEND INITIAL LINE TO it_functions .
ENDIF .

* Duplicate field per functions .
LOOP AT it_functions ASSIGNING <st_functions> .

st_fields_used = <st_fields_used> .

st_fields_used-functions = <st_functions>-function .

APPEND st_fields_used TO it_fields_used .

ENDLOOP.

ENDLOOP.

ENDFORM . "get_fields_used
*----------------------------------------------------------------------*
FORM get_statment
USING
it_fields_used TYPE tp_fields_used_tab
CHANGING
sql_statment TYPE string .

* Generate SQL statment .

DATA: it_text TYPE soli_tab.

CALL METHOD ob_gui_textedit_sql->get_text_as_r3table
IMPORTING
table = it_text
EXCEPTIONS
OTHERS = 1.

CALL METHOD cl_bcs_convert=>txt_to_string
EXPORTING
it_soli = it_text
RECEIVING
ev_string = sql_statment.

CONDENSE sql_statment .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF it_fields_used .

DATA: quoted TYPE string .
DATA: fields TYPE string .

DATA: tabix_1 TYPE sy-tabix .

LOOP AT it_fields_used ASSIGNING <st_fields_used> .

tabix_1 = sy-tabix .

IF tabix_1 GT 1 .
CONCATENATE fields ',' INTO fields .
ENDIF.

IF <st_fields_used>-functions IS INITIAL .

quoted = cl_my_string=>get_quoted( inp = <st_fields_used>-fcat-fieldname ) .

ELSE .

CASE <st_fields_used>-functions .
WHEN c_fun_count .
CONCATENATE <st_fields_used>-functions '(*)' INTO quoted.
WHEN OTHERS .

quoted = cl_my_string=>get_quoted( inp = <st_fields_used>-fcat-fieldname ) .

CONCATENATE <st_fields_used>-functions '(' quoted ')' INTO quoted.

ENDCASE .

ENDIF .

CONCATENATE fields quoted INTO fields SEPARATED BY space .

ENDLOOP.

REPLACE FIRST OCCURRENCE OF c_use_field_columns IN sql_statment WITH fields .

SHIFT sql_statment LEFT DELETING LEADING space .

IF sql_statment+0(6) NE 'select' .
MESSAGE 'select operation only ' TYPE 'E'.
ENDIF .

ENDFORM . "get_statment
*----------------------------------------------------------------------*
FORM run_statment
USING
sql_statment TYPE string
CHANGING
it_fields_used TYPE tp_fields_used_tab .

FIELD-SYMBOLS: <st_fields_used> LIKE LINE OF g_data-it_fields_used .

DATA: it_fieldcatalog TYPE lvc_t_fcat .
DATA: st_fieldcatalog LIKE LINE OF it_fieldcatalog .

DATA: cntr TYPE n LENGTH 3 .

*** Define and create dynamic table to recieve the result .
LOOP AT it_fields_used ASSIGNING <st_fields_used> .

cntr = sy-tabix .

CONCATENATE <st_fields_used>-fcat-fieldname cntr INTO st_fieldcatalog-fieldname .

<st_fields_used>-tempname = st_fieldcatalog-fieldname .

st_fieldcatalog-intlen = <st_fields_used>-fcat-intlen .

* st_fieldcatalog-fieldname = <st_fields_used>-fcat-fieldname
st_fieldcatalog-inttype = <st_fields_used>-fcat-inttype .
st_fieldcatalog-decimals = <st_fields_used>-fcat-decimals .

* Fix field size to accommodate functions result .
CASE <st_fields_used>-fcat-datatype .
WHEN 'INT2' OR 'INT4' OR 'INT1' OR
'DEC' OR 'QUAN' OR 'CURR' .
CASE <st_fields_used>-functions.
WHEN c_fun_sum OR c_fun_count .
st_fieldcatalog-intlen = 12 ."Hope that this is enough....
st_fieldcatalog-inttype = 'P' .
WHEN c_fun_avg .
st_fieldcatalog-inttype = 'P' .
WHEN OTHERS.
ENDCASE.

ENDCASE .

APPEND st_fieldcatalog TO it_fieldcatalog .

ENDLOOP .

DATA: st_fields_used LIKE LINE OF g_data-it_fields_used .

* Update the globals .
LOOP AT g_data-it_fields_used ASSIGNING <st_fields_used> .

<st_fields_used>-tempname = space .

READ TABLE it_fields_used INTO st_fields_used
WITH KEY
fcat-fieldname = <st_fields_used>-fcat-fieldname .

CHECK sy-subrc EQ 0 .

<st_fields_used>-tempname = st_fields_used-tempname .

ENDLOOP .

* DATA: it_data TYPE REF TO data .
DATA: st_data TYPE REF TO data .

FIELD-SYMBOLS: <it_data> TYPE STANDARD TABLE .
FIELD-SYMBOLS: <st_data> TYPE ANY .

CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = it_fieldcatalog
IMPORTING
ep_table = g_data-it_data.

ASSIGN g_data-it_data->* TO <it_data> .

DATA: it_tab_dref TYPE REF TO data .
DATA: it_str_dref TYPE REF TO data .

GET REFERENCE OF <it_data> INTO it_tab_dref.

CREATE DATA it_str_dref LIKE LINE OF <it_data> .
ASSIGN it_str_dref->* TO <st_data> .

*** Get the data.

DATA: ob_sql_connection TYPE REF TO cl_sql_connection .
DATA: ob_sql_statement TYPE REF TO cl_sql_statement .
DATA: ob_sql_result_set TYPE REF TO cl_sql_result_set .
DATA: ob_sql_exception TYPE REF TO cx_sql_exception .

DATA: rows_ret TYPE i .

TRY.

* CREATE OBJECT ob_sql_connection .
ob_sql_connection = cl_sql_connection=>get_connection( ) .

ob_sql_statement = ob_sql_connection->create_statement( ).
ob_sql_result_set = ob_sql_statement->execute_query( sql_statment ).

ob_sql_result_set->set_param_table( it_tab_dref ).

CHECK ob_sql_result_set IS BOUND .

ob_sql_result_set->next_package( ).

* Read the result (ob_sql_result_set) row by row
* ob_sql_result_set->set_param_struct( it_str_dref ).
* WHILE ob_sql_result_set->next( ) GT 0 .
* APPEND <st_data> TO <it_data> .
* ENDWHILE .

ob_sql_result_set->close( ).
ob_sql_connection->close( ) .

*** Update the output .

TRY.

CALL METHOD ob_salv_table_out->set_data
CHANGING
t_table = <it_data>.

DATA: st_fcat TYPE lvc_s_fcat .
DATA: ob_salv_columns TYPE REF TO cl_salv_columns_table .
DATA: ob_salv_aggregations TYPE REF TO cl_salv_aggregations.
DATA: it_column_ref TYPE salv_t_column_ref .
FIELD-SYMBOLS: <st_column_ref> LIKE LINE OF it_column_ref .

ob_salv_columns = ob_salv_table_out->get_columns( ) .
* ob_salv_columns->set_optimize( abap_true ) .

ob_salv_table_out->get_aggregations( RECEIVING value = ob_salv_aggregations ) .

ob_salv_columns->get( RECEIVING value = it_column_ref ) .

DATA: st_ddic_reference TYPE salv_s_ddic_reference .

DATA: functions_text TYPE string .
DATA: outlen_1 TYPE lvc_outlen .
DATA: outlen_2 TYPE lvc_outlen .

LOOP AT it_column_ref ASSIGNING <st_column_ref> .

READ TABLE it_fields_used ASSIGNING <st_fields_used>
WITH KEY
tempname = <st_column_ref>-columnname .

IF <st_fields_used>-functions IS NOT INITIAL .
CONCATENATE '(' <st_fields_used>-functions ')' INTO functions_text .
ELSE.
CLEAR functions_text .
ENDIF .

CONCATENATE <st_fields_used>-fcat-fieldname functions_text INTO st_fcat-tooltip .

<st_column_ref>-r_column->set_tooltip( value = st_fcat-tooltip ) .

st_ddic_reference-field = <st_fields_used>-fcat-ref_field .
st_ddic_reference-table = <st_fields_used>-fcat-ref_table .

<st_column_ref>-r_column->set_ddic_reference( value = st_ddic_reference ) .

CONCATENATE <st_fields_used>-fcat-scrtext_l functions_text INTO st_fcat-scrtext_l .
CONCATENATE <st_fields_used>-fcat-scrtext_m functions_text INTO st_fcat-scrtext_m .
CONCATENATE <st_fields_used>-fcat-scrtext_s functions_text INTO st_fcat-scrtext_s .

CONDENSE:st_fcat-scrtext_l, st_fcat-scrtext_m,st_fcat-scrtext_s .

<st_column_ref>-r_column->set_long_text( value = st_fcat-scrtext_l ) .
<st_column_ref>-r_column->set_medium_text( value = st_fcat-scrtext_m ) .
<st_column_ref>-r_column->set_short_text( value = st_fcat-scrtext_s ) .

outlen_1 = STRLEN( st_fcat-scrtext_m ) .
<st_column_ref>-r_column->get_output_length( RECEIVING value = outlen_2 ) .

IF outlen_1 GT outlen_2 .
<st_column_ref>-r_column->set_output_length( value = outlen_1 ) .
ENDIF .

IF <st_fields_used>-use_sum EQ abap_true OR
<st_column_ref>-columnname CS c_counter .

TRY .
ob_salv_aggregations->add_aggregation( columnname = <st_column_ref>-columnname ).
CATCH cx_salv_existing .
ENDTRY.

ENDIF .

* <st_column_ref>-r_column->set_alignment( value = if_salv_c_alignment=>left ) .

ENDLOOP .

CATCH cx_salv_no_new_data_allowed .
ENDTRY.

ob_salv_table_out->refresh( ) .

ob_salv_table_out->display( ) .

CATCH cx_sql_exception INTO ob_sql_exception.

ENDTRY.

PERFORM show_sql_feed_back
USING
sql_statment
ob_sql_exception .

ENDFORM . "run_statment
*----------------------------------------------------------------------*
FORM show_sql_feed_back
USING
sql_statment TYPE string
ob_sql_exception TYPE REF TO cx_sql_exception .

DATA: it_text TYPE soli_tab.

IF ob_sql_exception IS NOT INITIAL .

IF ob_sql_exception->db_error EQ abap_true ..
APPEND 'SQL error occured:' TO it_text .
APPEND ob_sql_exception->sql_code TO it_text .
APPEND ob_sql_exception->sql_message TO it_text .
ELSE.
APPEND 'Error from DBI (details in dev-trace):' TO it_text .
APPEND ob_sql_exception->internal_error TO it_text .
ENDIF.

ENDIF .

APPEND INITIAL LINE TO it_text .

DATA: it_soli TYPE soli_tab .

CALL METHOD cl_bcs_convert=>string_to_soli
EXPORTING
iv_string = sql_statment
RECEIVING
et_soli = it_soli.

APPEND LINES OF it_soli TO it_text .

CALL METHOD ob_gui_textedit_err->set_text_as_r3table
EXPORTING
table = it_text
EXCEPTIONS
error_dp = 1
error_dp_create = 2
OTHERS = 3.

ENDFORM . "show_sql_feed_back
*----------------------------------------------------------------------*
FORM set_alv_table_fld
CHANGING
ob_gui_container TYPE REF TO cl_gui_container .

DATA: it_ddfields TYPE ddfields .
FIELD-SYMBOLS: <st_ddfields> LIKE LINE OF it_ddfields .

CALL METHOD cl_salv_ddic=>get_by_data
EXPORTING
data = g_data-it_fields_used
RECEIVING
t_dfies = it_ddfields.

DATA: it_fieldcatalog TYPE lvc_t_fcat .
DATA: st_fieldcatalog LIKE LINE OF it_fieldcatalog .

DATA: fieldname TYPE fieldname .

LOOP AT it_ddfields ASSIGNING <st_ddfields> .

fieldname = <st_ddfields>-fieldname .

REPLACE FIRST OCCURRENCE OF '_FCAT' IN fieldname WITH '' .

CHECK 'use_field fieldname use_sum functions scrtext_m' CS fieldname .

CLEAR st_fieldcatalog .

st_fieldcatalog-fieldname = <st_ddfields>-fieldname .
st_fieldcatalog-reptext = <st_ddfields>-reptext .
st_fieldcatalog-rollname = <st_ddfields>-rollname .

CASE fieldname .

WHEN 'USE_FIELD' .

st_fieldcatalog-edit = abap_true .
st_fieldcatalog-checkbox = abap_true .
st_fieldcatalog-coltext = 'Select'(h01) .
st_fieldcatalog-outputlen = 6 .

WHEN 'FIELDNAME' .

st_fieldcatalog-hotspot = abap_true .

WHEN 'USE_SUM' .

st_fieldcatalog-edit = abap_true .
st_fieldcatalog-checkbox = abap_true .
st_fieldcatalog-coltext = 'Sum'(h03) .
st_fieldcatalog-outputlen = 5 .

WHEN 'FUNCTIONS' .

st_fieldcatalog-edit = abap_true .
st_fieldcatalog-dd_outlen = 128 .
st_fieldcatalog-outputlen = 15 .
st_fieldcatalog-coltext = 'SQL function'(h02) .

WHEN 'SCRTEXT_M' .

st_fieldcatalog-outputlen = 20 .

ENDCASE .

APPEND st_fieldcatalog TO it_fieldcatalog .

ENDLOOP .

CREATE OBJECT ob_gui_alv_grid_fld
EXPORTING
i_parent = ob_gui_container.

* registers the event handlers
SET HANDLER ob_event_receiver->toolbar
ob_event_receiver->hotspot_click
ob_event_receiver->user_command
FOR ob_gui_alv_grid_fld .

CALL METHOD ob_gui_alv_grid_fld->register_edit_event
EXPORTING
i_event_id = cl_gui_alv_grid=>mc_evt_modified.

DATA: st_layout TYPE lvc_s_layo .

st_layout-no_rowmark = abap_true .
* st_layout-cwidth_opt = abap_true .
st_layout-stylefname = c_styl_fname .

CALL METHOD ob_gui_alv_grid_fld->set_table_for_first_display
EXPORTING
is_layout = st_layout
CHANGING
it_fieldcatalog = it_fieldcatalog
it_outtab = g_data-it_fields_used.

ENDFORM. "set_alv_table_fld
*----------------------------------------------------------------------*
FORM set_alv_table_out
CHANGING
ob_gui_container TYPE REF TO cl_gui_container .

DATA: it_table TYPE TABLE OF scarr .

TRY .

CALL METHOD cl_salv_table=>factory
EXPORTING
list_display = if_salv_c_bool_sap=>false
r_container = ob_gui_container
IMPORTING
r_salv_table = ob_salv_table_out
CHANGING
t_table = it_table.

DATA: ob_salv_functions TYPE REF TO cl_salv_functions .

ob_salv_functions = ob_salv_table_out->get_functions( ) .
ob_salv_functions->set_all( abap_true ) .

DATA: ob_salv_events TYPE REF TO cl_salv_events_table.

ob_salv_events = ob_salv_table_out->get_event( ).

SET HANDLER ob_event_receiver->double_click FOR ob_salv_events .

CATCH cx_salv_msg .
ENDTRY .

ENDFORM. "set_alv_table_out

*GUI Texts
*----------------------------------------------------------
* TITLE_COMMON --> &1

*Selection texts
*----------------------------------------------------------
* P_CONNAM D .
* P_TABNAM D .

*Messages
*----------------------------------------------------------
*
* Message class: E2
*007 & does not exist. Check name
*
* Message class: Hard coded
* select operation only
*
* Message class: MO
*419 You are not authorized to display this table

----------------------------------------------------------------------------------
Extracted by Mass Download version 1.5.2 - E.G.Mellodew. 1998-2018. Sap Release 701

[former_member791758]

Hi Eitan,

 

Could you please share the code of Y_R_EITAN_TEST_31_03 -

The case of “dynamic columns in smartform”

I am unable to find the code on any link.

 

You help will be much appreciated.