*&---------------------------------------------------------------------* *& Report ZZSQL_QUERY *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------* REPORT zzsql_query. TYPE-POOLS: icon, rsfs, rsds, slis. TABLES: sscrfields. "Campos en las imágenes de selección DATA: g_repid LIKE sy-repid. DATA: delim TYPE c. DATA: code TYPE TABLE OF rssource-line, prog(8) TYPE c, msg(120) TYPE c, lin(3) TYPE c, wrd(10) TYPE c, off(3) TYPE c. DATA: onelinecode LIKE LINE OF code. DATA : fcat TYPE slis_t_fieldcat_alv. DATA : wcat LIKE LINE OF fcat. CONSTANTS: c_line_length TYPE i VALUE 80. * define table type for data exchange TYPES: BEGIN OF mytable_line, line(c_line_length) TYPE c, END OF mytable_line. TYPES: ty_table TYPE TABLE OF mytable_line. DATA: BEGIN OF ti_dd03l OCCURS 0, tabname LIKE dd03l-tabname, fieldname LIKE dd03l-fieldname, position LIKE dd03l-position, END OF ti_dd03l. * Tablas con los datos de la consulta. DATA: ti_select TYPE ty_table, ti_from TYPE ty_table, ti_where TYPE ty_table, ti_tmp TYPE ty_table. DATA: myline LIKE LINE OF ti_select. DATA: functxt TYPE smp_dyntxt. *----------------------------------------------------------------------* * PARAMETROS *----------------------------------------------------------------------* SELECTION-SCREEN BEGIN OF BLOCK bl01 WITH FRAME TITLE tit . SELECTION-SCREEN: BEGIN OF LINE. SELECTION-SCREEN: PUSHBUTTON 10(45) sel USER-COMMAND sel VISIBLE LENGTH 12. SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: SKIP. SELECTION-SCREEN: BEGIN OF LINE. SELECTION-SCREEN: PUSHBUTTON 10(45) frm USER-COMMAND frm VISIBLE LENGTH 12. SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: SKIP. SELECTION-SCREEN: BEGIN OF LINE. SELECTION-SCREEN: PUSHBUTTON 10(45) whr USER-COMMAND whr VISIBLE LENGTH 12. SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN END OF BLOCK bl01. SELECTION-SCREEN: BEGIN OF LINE, COMMENT 1(15) text1, POSITION 20. PARAMETER: rows TYPE i. "Up To N rows SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: BEGIN OF LINE, COMMENT 1(15) text2, POSITION 20. PARAMETER: down AS CHECKBOX. "Download SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: BEGIN OF LINE, COMMENT 1(16) text3, POSITION 20. PARAMETER: conv AS CHECKBOX. "Conversión SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: BEGIN OF LINE, COMMENT 1(16) text4, POSITION 20. PARAMETER: tec AS CHECKBOX. "Nombre técnico SELECTION-SCREEN: END OF LINE. * Botón de Borrar todo en el STATUS GUI. SELECTION-SCREEN: FUNCTION KEY 1. INITIALIZATION. tit = 'Parámetros:'. text1 = 'Up To N rows'. text2 = 'Download code'. text3 = 'Sin conv. x dom.'. text4 = 'Nombre técnico'. * Agrego el boton de borrar todo en STATUS GUI. functxt-icon_id = icon_delete. functxt-quickinfo = 'Borrar todo'. sscrfields-functxt_01 = functxt. * Creo la visualización del botón de SELECT en la DYNPRO. CALL FUNCTION 'ICON_CREATE' EXPORTING name = icon_select_detail text = 'SELECT' info = 'Visualizar SELECT' IMPORTING RESULT = sel EXCEPTIONS OTHERS = 0. * Creo la visualización del botón de FROM en la DYNPRO. CALL FUNCTION 'ICON_CREATE' EXPORTING name = icon_table_settings text = 'FROM' info = 'Visualizar FROM' IMPORTING RESULT = frm EXCEPTIONS OTHERS = 0. * creo la visualización del botón de WHERE en la dynpro. CALL FUNCTION 'ICON_CREATE' EXPORTING name = icon_filter text = 'WHERE' info = 'Visualizar WHERE' IMPORTING RESULT = whr EXCEPTIONS OTHERS = 0. IMPORT ti_select FROM MEMORY ID 'ZSELECT'. IMPORT ti_from FROM MEMORY ID 'ZFROM'. IMPORT ti_where FROM MEMORY ID 'ZWHERE'. *----------------------------------------------------------------------- * AT SELECTION-SCREEN *----------------------------------------------------------------------- AT SELECTION-SCREEN. CASE sy-ucomm. WHEN 'FC01'. REFRESH: ti_select, ti_where, ti_from. FREE MEMORY ID: 'ZSELECT', 'ZFROM', 'ZWHERE'. WHEN 'SEL'. PERFORM editor_table USING ' ' 'SELECT' CHANGING ti_select[]. WHEN 'FRM'. PERFORM editor_table USING ' ' 'FROM' CHANGING ti_from[]. WHEN 'WHR'. PERFORM editor_table USING ' ' 'WHERE' CHANGING ti_where[]. WHEN OTHERS. * Do Nothing !!. ENDCASE. START-OF-SELECTION. PERFORM f_runsql. ************************************************************************ * F O R M S ************************************************************************ *&---------------------------------------------------------------------* *& Form F_RUNSQL *&---------------------------------------------------------------------* FORM f_runsql. DATA: first TYPE i, numcols TYPE i, aggfun TYPE i, pos TYPE i, off TYPE i, len TYPE i, numrows TYPE i, rownum TYPE i, mystring TYPE string, mystring2 TYPE string, crows(8) TYPE c. DATA: l_tfill TYPE sy-tfill, l_single TYPE c, l_todos TYPE c, l_tabix LIKE sy-tabix, l_carac TYPE c, l_table LIKE rsrd1-tbma_val, l_join TYPE c, l_cant TYPE i. DATA : BEGIN OF ti_val OCCURS 0, val(80), END OF ti_val. DATA : BEGIN OF tblcol_tab OCCURS 0, tbl TYPE string, col TYPE string. DATA : END OF tblcol_tab. FIELD-SYMBOLS: TYPE mytable_line. CHECK ti_select[] IS NOT INITIAL AND ti_from[] IS NOT INITIAL. EXPORT ti_select TO MEMORY ID 'ZSELECT'. EXPORT ti_from TO MEMORY ID 'ZFROM'. EXPORT ti_where TO MEMORY ID 'ZWHERE'. CLEAR: code, l_join, l_table, l_todos. REFRESH code. MOVE 0 TO : first, numcols, aggfun. * ##################### * ####### FROM ######## * ##################### * Verifico si se realiza un JOIN. SEARCH ti_from FOR 'JOIN'. IF sy-subrc EQ 0. l_join = 'X'. CLEAR l_table. ENDIF. IF l_join IS INITIAL. * Busco el nombre de la tabla. SEARCH ti_from FOR 'FROM'. IF sy-subrc EQ 0. l_tabix = sy-tabix. DO. READ TABLE ti_from INTO myline INDEX l_tabix. IF sy-subrc NE 0. EXIT. ENDIF. TRANSLATE myline TO UPPER CASE. CONDENSE myline. REFRESH ti_val. SPLIT myline AT space INTO TABLE ti_val. LOOP AT ti_val WHERE val NE 'FROM'. l_table = ti_val-val. EXIT. ENDLOOP. IF sy-subrc EQ 0. EXIT. ELSE. ADD 1 TO l_tabix. ENDIF. ENDDO. ELSE. CALL FUNCTION 'POPUP_TO_INFORM' EXPORTING titel = g_repid txt2 = 'FROM DMLs Only Please!' txt1 = 'Correct & Retry'. STOP. ENDIF. ENDIF. * ##################### * ###### SELECT ####### * ##################### ti_tmp[] = ti_select[]. * Busco el select. SEARCH ti_tmp FOR 'SELECT'. IF sy-subrc NE 0. * Falta el SELECT. CALL FUNCTION 'POPUP_TO_INFORM' EXPORTING titel = g_repid txt2 = 'SELECT DMLs Only Please!' txt1 = 'Correct & Retry'. STOP. ELSE. READ TABLE ti_tmp INTO myline INDEX sy-tabix. TRANSLATE myline TO UPPER CASE. REPLACE 'SELECT' IN myline WITH space. CONDENSE myline. MODIFY ti_tmp FROM myline INDEX sy-tabix. ENDIF. SEARCH ti_tmp FOR 'SINGLE'. IF sy-subrc EQ 0. * Se trata de un SELECT SINGLE. l_single = 'X'. READ TABLE ti_tmp INTO myline INDEX sy-tabix. TRANSLATE myline TO UPPER CASE. REPLACE 'SINGLE' IN myline WITH space. CONDENSE myline. MODIFY ti_tmp FROM myline INDEX sy-tabix. ELSE. CLEAR l_single. ENDIF. IF l_join IS INITIAL. LOOP AT ti_tmp INTO myline. CONDENSE myline. off = STRLEN( myline ). IF off = 0. CONTINUE. ELSE. * Verifico si se requieren todos los campos de la tabla. DO. l_carac = myline+pos(1). IF l_carac = '*'. * Tengo que buscar todos los campos de la tabla para poder * visualizarlos. PERFORM buscar_campos TABLES ti_dd03l USING l_table. l_todos = 'X'. EXIT. ELSE. IF pos < off. ADD 1 TO pos. ELSE. EXIT. ENDIF. ENDIF. ENDDO. ENDIF. ENDLOOP. ENDIF. IF l_todos IS INITIAL. LOOP AT ti_tmp INTO myline. IF l_join EQ 'X'. * '~' TRANSLATE myline TO UPPER CASE. CONDENSE myline. REFRESH ti_val. SPLIT myline AT space INTO TABLE ti_val. LOOP AT ti_val WHERE val NE space. SPLIT ti_val-val AT '~' INTO tblcol_tab-tbl tblcol_tab-col. APPEND tblcol_tab. ENDLOOP. ELSE. * Se seleccionaron varios campos. TRANSLATE myline TO UPPER CASE. CONDENSE myline. REFRESH ti_val. SPLIT myline AT space INTO TABLE ti_val. LOOP AT ti_val WHERE val NE space. tblcol_tab-tbl = l_table. tblcol_tab-col = ti_val-val. APPEND tblcol_tab. ENDLOOP. ENDIF. ENDLOOP. ELSE. * Cargo los campos del '*' (Asterisco). DESCRIBE TABLE ti_dd03l LINES l_tfill. * IF l_tfill = 99. LOOP AT ti_select ASSIGNING . * Elimino el '*' (Asterisco), ya que solo se pueden visualizar * 99 campos en un ALV. TRANSLATE -line USING '* '. ENDLOOP. * ENDIF. REFRESH tblcol_tab. LOOP AT ti_dd03l. tblcol_tab-tbl = ti_dd03l-tabname. tblcol_tab-col = ti_dd03l-fieldname. APPEND tblcol_tab. * CHECK l_tfill = 99. * Cargo los campos, ya que saque el '*' (Asterisco). APPEND ti_dd03l-fieldname TO ti_select. ENDLOOP. ENDIF. IF tblcol_tab[] IS INITIAL. * No hay ningún campo seleccionado. CALL FUNCTION 'POPUP_TO_INFORM' EXPORTING titel = g_repid txt2 = 'Table~Column Open SQL is MUST' txt1 = 'No Fields'. STOP. ENDIF. * ################################### * ###### Genero del REPORT ########## * ################################### * Nota: Se toma como linea para el código fuente, 72 caracteres. MOVE 'ZSUBPOOL' TO prog. APPEND 'REPORT ZSUBPOOL.' TO code. APPEND '' TO code. APPEND 'Form DoSQL USING rows TYPE i.' TO code. APPEND '' TO code. * Creo la tabla interna que contiene los datos. APPEND 'data : begin of TI_TAB occurs 0,' TO code. DESCRIBE TABLE tblcol_tab LINES numrows . CLEAR rownum. LOOP AT tblcol_tab. ADD 1 TO rownum. WRITE rownum TO crows. CONCATENATE 'WFLD' crows INTO mystring. CONDENSE mystring NO-GAPS. CONCATENATE mystring 'Like' tblcol_tab-tbl INTO mystring SEPARATED BY space. IF rownum = numrows. CONCATENATE mystring '-' tblcol_tab-col '.' INTO mystring. ELSE. CONCATENATE mystring '-' tblcol_tab-col ',' INTO mystring. ENDIF. APPEND mystring TO code. ENDLOOP. APPEND 'data : end of TI_TAB.' TO code. APPEND '' TO code. APPEND 'Type-Pools : Slis.' TO code. APPEND '' TO code. APPEND 'DATA : l_repid LIKE sy-repid.' TO code. APPEND 'DATA : fcat TYPE SLIS_T_FIELDCAT_ALV.' TO code. APPEND 'DATA : wcat LIKE LINE OF FCAT.' TO code. APPEND 'DATA : MyString type STRING.' TO code. APPEND 'DATA : MyTitle type LVC_TITLE.' TO code. APPEND '' TO code. APPEND 'MOVE sy-repid TO l_repid.' TO code. APPEND '' TO code. APPEND '* Create Field Catalogue' TO code. CLEAR rownum. LOOP AT tblcol_tab. ADD 1 TO rownum. WRITE rownum TO crows. CONCATENATE 'WFLD' crows INTO mystring. CONDENSE mystring NO-GAPS. CONCATENATE ' wcat-fieldname = ' '''' mystring '''' '.' INTO mystring. APPEND mystring TO code. CONCATENATE ' wcat-tabname = ' '''TI_TAB''' '.' INTO mystring. APPEND mystring TO code. CONCATENATE ' wcat-ref_fieldname = ' '''' tblcol_tab-col '''.' INTO mystring. APPEND mystring TO code. CONCATENATE ' wcat-ref_tabname = ' '''' tblcol_tab-tbl '''.' INTO mystring. APPEND mystring TO code. IF conv IS NOT INITIAL. * Se setea el catálogo para que no realice la conversión definida * en el dominio del campo. CONCATENATE ' wcat-no_convext = ' '''X''' '.' INTO mystring. ELSE. CONCATENATE ' wcat-no_convext = ' ''' ''' '.' INTO mystring. ENDIF. APPEND mystring TO code. IF tec IS NOT INITIAL. * Se visualiza el nombre técnico de la columna. CONCATENATE ' wcat-seltext_l = ' 'wcat-seltext_m =' INTO mystring SEPARATED BY space. APPEND mystring TO code. CONCATENATE ' wcat-seltext_s = ' 'wcat-reptext_ddic = ' INTO mystring SEPARATED BY space. APPEND mystring TO code. CONCATENATE ' ''' tblcol_tab-col '''.' INTO mystring. APPEND mystring TO code. ENDIF. APPEND ' Append Wcat to Fcat.' TO code. ENDLOOP. APPEND '' TO code. APPEND 'Try.' TO code. APPEND '' TO code. MOVE 0 TO first. LOOP AT ti_select INTO myline. APPEND myline TO code. ENDLOOP. IF l_single IS INITIAL. APPEND 'Into Table TI_TAB' TO code. ELSE. APPEND 'Into TI_TAB' TO code. ENDIF. IF NOT rows IS INITIAL AND l_single IS INITIAL. APPEND 'Up To ROWS rows' TO code. ENDIF. LOOP AT ti_from INTO myline. APPEND myline TO code. ENDLOOP. LOOP AT ti_where INTO myline. APPEND myline TO code. ENDLOOP. APPEND '.' TO code. APPEND '' TO code. IF NOT l_single IS INITIAL. APPEND 'APPEND TI_TAB.' TO code. ENDIF. APPEND '' TO code. APPEND 'CALL FUNCTION ''REUSE_ALV_LIST_DISPLAY''' TO code. APPEND ' EXPORTING' TO code. APPEND ' IT_FIELDCAT = FCAT' TO code. APPEND ' TABLES' TO code. APPEND ' T_OUTTAB = TI_TAB.' TO code. APPEND ' .' TO code. APPEND '' TO code. APPEND 'Catch CX_ROOT.' TO code. APPEND 'CALL FUNCTION ''POPUP_TO_INFORM''' TO code. APPEND ' EXPORTING' TO code. APPEND ' titel = ''jncDynamicSub''' TO code. APPEND ' txt2 = ''Generate SUBROUTINE POOL Succeeded BUT SQL failed''' TO code. APPEND ' txt1 = ''Possible Wrong SQL - see c:\jnc.ab4''.' TO code. APPEND 'EndTry.' TO code. APPEND 'EndForm. "DoSQL.' TO code. IF down IS NOT INITIAL. * Download del código fuente. CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING filename = 'C:\SQL.txt' TABLES data_tab = code. ENDIF. GENERATE SUBROUTINE POOL code NAME prog MESSAGE msg LINE lin WORD wrd OFFSET off. IF sy-subrc <> 0. CALL FUNCTION 'POPUP_TO_INFORM' EXPORTING titel = g_repid txt2 = 'Generate SUBROUTINE POOL Failed' txt1 = msg. ELSE. PERFORM dosql IN PROGRAM (prog) USING rows IF FOUND. IF sy-subrc <> 0. CALL FUNCTION 'POPUP_TO_INFORM' EXPORTING titel = g_repid txt2 = 'Generate SUBROUTINE POOL Succeeded BUT Call failed' txt1 = 'Possible Wrong SQL - see c:\jnc.ab4'. ENDIF. ENDIF. ENDFORM. "F_RUNSQL *&---------------------------------------------------------------------* *& Form buscar_campos *&---------------------------------------------------------------------* * Busco todos los campos de la tabla seleccionada. *----------------------------------------------------------------------* * -->PT_DD03L Tabla con los campos. * -->P_TABLE Tabla. *----------------------------------------------------------------------* FORM buscar_campos TABLES pt_dd03l STRUCTURE ti_dd03l USING p_table. SELECT tabname fieldname position INTO CORRESPONDING FIELDS OF TABLE pt_dd03l FROM dd03l UP TO 99 ROWS WHERE tabname EQ p_table AND NOT fieldname LIKE '.INCLU%' AND datatype NE 'CLNT'. SORT pt_dd03l BY position. ENDFORM. " buscar_campos *&---------------------------------------------------------------------* *& Form editor_table *&---------------------------------------------------------------------* * Editor de texto. *----------------------------------------------------------------------* * -->PT_LINES Líneas del texto. * -->P_DISPLAY Visualizar o editar. *----------------------------------------------------------------------* FORM editor_table USING p_display TYPE s38e-app_disp p_name TYPE trdir-name * CHANGING pt_lines TYPE mytable_line. CHANGING pt_lines TYPE ty_table. DATA: l_changed LIKE s38e-buf_varied, l_subrc LIKE sy-subrc. CALL FUNCTION 'EDITOR_TABLE' EXPORTING display = p_display name = p_name IMPORTING changed = l_changed subrc = l_subrc TABLES content = pt_lines. ENDFORM. " editor_table