当前位置：首页 > backend >正文

搭建动态SQL取数

backend 2025/5/10 1:20:33

日常取数的时候可能会存在动态SQL的问题，比如取数动态或者条件动态等情况，下面针对动态SQL做一个完整的处理。包括SELECT 、FROM、WHERE 以及最后table的动态。

首先数据定义，这里全按照表来append处理

TYPES:BEGIN OF ty_data,edpline TYPE edpline,END OF ty_data.DATA:lt_select TYPE TABLE OF ty_data,ls_select TYPE ty_data,lt_from   TYPE TABLE OF ty_data,ls_from   TYPE ty_data,lt_where  TYPE TABLE OF ty_data,ls_where  TYPE ty_data.

动态SELECT 处理


CLEAR:ls_select.REFRESH:lt_select.ls_select-edpline = 'mara~matnr,'.
APPEND ls_select TO lt_select.CLEAR:ls_select.ls_select-edpline = 'makt~maktx,'.
APPEND ls_select TO lt_select.CLEAR:ls_select.ASSIGN lt_select[ lines( lt_select ) ] TO FIELD-SYMBOL(<comp>).
REPLACE ',' IN <comp> WITH ''.

动态表关联


CLEAR:ls_from.REFRESH:lt_from.ls_from-edpline = 'MARA'.
APPEND ls_from TO lt_from.CLEAR:ls_from.
ls_from-edpline = 'INNER JOIN MAKT'.
APPEND ls_from TO lt_from.CLEAR:ls_from.
ls_from-edpline = 'ON MARA~MATNR = MAKT~MATNR'.
APPEND ls_from TO lt_from.CLEAR:ls_from.

动态where 条件


CLEAR:ls_where.REFRESH:lt_where.ls_where = 'MAKT~SPRAS = @SY-LANGU'.
APPEND ls_where TO lt_where.CLEAR:ls_where.ls_where = 'AND MARA~MTART = `Z120`'.
APPEND ls_where TO lt_where.CLEAR:ls_where.

搭建动态表


***构建动态表DATA:gt_fieldcat TYPE lvc_t_fcat,gs_fieldcat TYPE lvc_s_fcat.DATA: dy_table TYPE REF TO data,dy_line  TYPE REF TO data.
FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,<dyn_wa>.DEFINE fill_field.CLEAR gs_fieldcat.gs_fieldcat-fieldname     = &1.gs_fieldcat-coltext       = &2.gs_fieldcat-ref_table     = &3.gs_fieldcat-ref_field     = &4.gs_fieldcat-scrtext_l = gs_fieldcat-scrtext_m = gs_fieldcat-scrtext_s = gs_fieldcat-reptext = gs_fieldcat-coltext.APPEND gs_fieldcat TO gt_fieldcat.
END-OF-DEFINITION.fill_field 'MATNR' '物料号' 'MARA' 'MATNR'.
fill_field 'MAKTX' '物料描述' 'MAKT' 'MAKTX'.* 根据it_fact生成动态表
CALL METHOD cl_alv_table_create=>create_dynamic_tableEXPORTINGit_fieldcatalog = gt_fieldcat[]IMPORTINGep_table        = dy_table.ASSIGN dy_table->* TO <dyn_table>.CREATE DATA dy_line LIKE LINE OF <dyn_table>.ASSIGN dy_line->* TO <dyn_wa>.

SQL 以及展示


SELECT (lt_select)FROM (lt_from)INTO TABLE @<dyn_table>WHERE (lt_where).LOOP AT <dyn_table> ASSIGNING <dyn_wa>.ASSIGN COMPONENT 'MATNR' OF STRUCTURE <dyn_wa> TO FIELD-SYMBOL(<fv_matnr>).ASSIGN COMPONENT 'MAKTX' OF STRUCTURE <dyn_wa> TO FIELD-SYMBOL(<fv_maktx>).WRITE:/ <fv_matnr>,<fv_maktx>.ENDLOOP.

完整代码

TYPES:BEGIN OF ty_data,edpline TYPE edpline,END OF ty_data.DATA:lt_select TYPE TABLE OF ty_data,ls_select TYPE ty_data,lt_from   TYPE TABLE OF ty_data,ls_from   TYPE ty_data,lt_where  TYPE TABLE OF ty_data,ls_where  TYPE ty_data.TYPES:BEGIN OF gy_matnr,matnr TYPE mara-matnr,maktx TYPE makt-maktx,END OF gy_matnr.
DATA:gs_matnr TYPE gy_matnr,gt_matnr TYPE TABLE OF gy_matnr.DATA:ls_data TYPE REF TO data.
FIELD-SYMBOLS:<ft_data> TYPE ANY TABLE.CLEAR:ls_select.REFRESH:lt_select.ls_select-edpline = 'mara~matnr,'.
APPEND ls_select TO lt_select.CLEAR:ls_select.ls_select-edpline = 'makt~maktx,'.
APPEND ls_select TO lt_select.CLEAR:ls_select.ASSIGN lt_select[ lines( lt_select ) ] TO FIELD-SYMBOL(<comp>).
REPLACE ',' IN <comp> WITH ''.CLEAR:ls_from.REFRESH:lt_from.ls_from-edpline = 'MARA'.
APPEND ls_from TO lt_from.CLEAR:ls_from.
ls_from-edpline = 'INNER JOIN MAKT'.
APPEND ls_from TO lt_from.CLEAR:ls_from.
ls_from-edpline = 'ON MARA~MATNR = MAKT~MATNR'.
APPEND ls_from TO lt_from.CLEAR:ls_from.CLEAR:ls_where.REFRESH:lt_where.ls_where = 'MAKT~SPRAS = @SY-LANGU'.
APPEND ls_where TO lt_where.CLEAR:ls_where.ls_where = 'AND MARA~MTART = `Z120`'.
APPEND ls_where TO lt_where.CLEAR:ls_where.***构建动态表DATA:gt_fieldcat TYPE lvc_t_fcat,gs_fieldcat TYPE lvc_s_fcat.DATA: dy_table TYPE REF TO data,dy_line  TYPE REF TO data.
FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,<dyn_wa>.DEFINE fill_field.CLEAR gs_fieldcat.gs_fieldcat-fieldname     = &1.gs_fieldcat-coltext       = &2.gs_fieldcat-ref_table     = &3.gs_fieldcat-ref_field     = &4.gs_fieldcat-scrtext_l = gs_fieldcat-scrtext_m = gs_fieldcat-scrtext_s = gs_fieldcat-reptext = gs_fieldcat-coltext.APPEND gs_fieldcat TO gt_fieldcat.
END-OF-DEFINITION.fill_field 'MATNR' '物料号' 'MARA' 'MATNR'.
fill_field 'MAKTX' '物料描述' 'MAKT' 'MAKTX'.* 根据it_fact生成动态表
CALL METHOD cl_alv_table_create=>create_dynamic_tableEXPORTINGit_fieldcatalog = gt_fieldcat[]IMPORTINGep_table        = dy_table.ASSIGN dy_table->* TO <dyn_table>.CREATE DATA dy_line LIKE LINE OF <dyn_table>.ASSIGN dy_line->* TO <dyn_wa>.SELECT (lt_select)FROM (lt_from)INTO TABLE @<dyn_table>WHERE (lt_where).LOOP AT <dyn_table> ASSIGNING <dyn_wa>.ASSIGN COMPONENT 'MATNR' OF STRUCTURE <dyn_wa> TO FIELD-SYMBOL(<fv_matnr>).ASSIGN COMPONENT 'MAKTX' OF STRUCTURE <dyn_wa> TO FIELD-SYMBOL(<fv_maktx>).WRITE:/ <fv_matnr>,<fv_maktx>.ENDLOOP.BREAK-POINT.

这里只是一个简单的代码，但是由简到难道理都一样，看具体需求去做具体的处理。
简单跑一下代码实现结果
在这里插入图片描述