Native SQL Program and ALV Layout

Question

I created a program using from tCode = SE38 I am using Native SQL.

Here is my code:

DATA: BEGIN OF GetData OCCURS 0,
        AUFNR Type COAS-AUFNR,
        AUART     Type COAS-AUART,
        END OF GetData.

EXEC SQL PERFORMING loop_output.
SELECT AUFNR, AUART 
INTO STRUCTURE :GetData 
FROM Mytable
Where (MANDT = 450)
ENDEXEC.
FORM loop_output.

  WRITE: / GetData-AUFNR,
        GetData- AUART.
ENDFORM.

Everything is working very well.

Now I want to add this report to ALV layout, how can I do this?

i did this : wa-fieldname = 'Month '. wa-seltext_m = 'nMonth '. APPEND wa TO fieldcatalog. CLEAR wa. * "2 wa-fieldname = 'Year '. wa-seltext_m = 'nYear'. APPEND wa TO fieldcatalog. CLEAR wa. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING IT_FIELDCAT = fieldcatalog TABLES T_OUTTAB = GetData. IF SY-SUBRC <> 0. * Implement suitable error handling here ENDIF. the result is empty. it returns the columns but the result is empty. please advise. thanks, — Ramzy Nashaat, Mar 31 '14 at 15:03
If you have additional information you can edit you question; that's better the making a comment. I improved your formatting to show the code (indent by 4 spaces). — knut, Mar 31 '14 at 17:52
If you want to use the native SQL, at least do it with help of the ADBC (for example classes CL_SQL_STATEMENT and CL_SQL_RESULT_SET). Second of all, use it only when you want to use specific features of a given database, for example `XMLSERIALIZE` from Oracle. In your example you use standard SQL, please use Open SQL for such purposes. — Jagger, Apr 01 '14 at 07:41

score 1 · Answer 1 · answered Apr 01 '14 at 01:53

Ok first of all i would create your structure as a type, then create a table of that type to pass to SALV. I am using SALV in this example because it is far easier. You need a table type to pass to the SALV as a structure format, which it wouldnt recognise currently with your declarations. I removed the mandt where clause in my code because in our system we cannot query by client. The perform sets up the alv settings, and then the display method executes it.

TYPES: BEGIN OF ty_getdata,
         aufnr TYPE coas-aufnr,
         auart     TYPE coas-auart,
         END OF ty_getdata.

 DATA: lt_getdata type TABLE OF ty_getdata.

 DATA: gr_salv      TYPE REF TO cl_salv_table,
       gr_functions TYPE REF TO cl_salv_functions,
       gr_display   TYPE REF TO cl_salv_display_settings,
       gr_columns   TYPE REF TO cl_salv_columns.

 SELECT aufnr auart
   INTO CORRESPONDING FIELDS OF TABLE lt_getdata
 FROM mytable.

   PERFORM alvsettings.

   gr_salv->display( ).

*&---------------------------------------------------------------------*
*&      Form  alvsettings
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM alvsettings .

  cl_salv_table=>factory( IMPORTING r_salv_table = gr_salv
                          CHANGING t_table = lt_getdata ).

  gr_functions = gr_salv->get_functions( ).
  gr_functions->set_all( abap_true ).

  gr_display = gr_salv->get_display_settings( ).
  gr_display->set_striped_pattern( cl_salv_display_settings=>true ).
  gr_display->set_list_header( 'SALV Output' ).

  gr_columns = gr_salv->get_columns( ).
  gr_columns->set_optimize( 'X' ).

ENDFORM.                    " alvsettings

score 1 · Answer 2 · answered Aug 14 '15 at 08:25

Here is an example with some comments.

First, Datadefinition

TYPE-POOLS slis. "import you need for REUSE_ALV_FIELDCATALOG_MERGE

DATA:
  lt_fieldcat TYPE slis_t_fieldcat_alv,

  BEGIN OF G_IT_MATERIAL occurs 0,
    MATNR LIKE MARA-MATNR,
    MTART LIKE MARA-MTART,
    MAKTX_DE LIKE MAKT-MAKTX,
    MAKTX_FR LIKE MAKT-MAKTX,
    MAKTX_IT LIKE MAKT-MAKTX,
    ERNAM LIKE MARA-ERNAM,
    ERSDA LIKE MARA-ERSDA,
    LAEDA LIKE MARA-LAEDA,
  END OF G_IT_MATERIAL.

It is absolutley necesssairy that you define your local structur directly with LIKE, otherwise the parser from REUSE_ALV_FIELDCATALOG_MERGE will not find it.

select your stuff:

 SELECT ma~matnr ma~mtart ma~ernam ma~ersda ma~laeda
 de~maktx as maktx_de fr~maktx as maktx_fr it~maktx as maktx_it
 FROM mara as ma
 LEFT JOIN MAKT as de ON de~matnr = ma~matnr AND de~spras = 'DE'
 LEFT JOIN MAKT as fr ON fr~matnr = ma~matnr AND fr~spras = 'FR'
 LEFT JOIN MAKT as it ON it~matnr = ma~matnr AND it~spras = 'IT'
 INTO CORRESPONDING FIELDS OF TABLE g_it_material
      WHERE ...

create dynamicliy a field catalog

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
EXPORTING
I_PROGRAM_NAME        = sy-repid

I_INTERNAL_TABNAME    = 'G_IT_MATERIAL'

I_INCLNAME            = sy-repid
CHANGING
ct_fieldcat            = lt_fieldcat
EXCEPTIONS
inconsistent_interface = 1
program_error          = 2
OTHERS                 = 3.

IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

now display that ALV grid

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
it_fieldcat   = lt_fieldcat                 "you could also give a structure
"i_structure_name      = 'ZMM_SMATERIAL'    "here instead of the fieldcat
TABLES
t_outtab      = g_it_material
EXCEPTIONS
program_error = 1
OTHERS        = 2.

Note that the parser also needs a max linesize of 72.

Native SQL Program and ALV Layout

2 Answers2