OpenSQL动态INTO子句：用户可选字段查询的实现问询

Got it, let's tackle this problem. The core issue here is moving from rigid, mandatory key parameters to a flexible setup where users can provide any number of filter conditions (including zero) for their selected table and fields. Here's how to adjust your implementation step by step:

1. Adjust the Selection Screen to Allow Optional Inputs #

First, remove the OBLIGATORY flag from your key parameters so users aren't forced to fill all three. For even more flexibility, replace fixed single parameters with a table control to let users add as many filter pairs (field + value) as they need.

Example: Optional Single Parameters #

PARAMETERS: p_table TYPE tabname OBLIGATORY, " Still mandatory since we need a target table
            p_fld1 TYPE fieldname, " User-specified fields (could also use a multi-select list)
            p_fld2 TYPE fieldname,
            p_fld3 TYPE fieldname,
            p_key1 TYPE string, " No OBLIGATORY anymore
            p_key2 TYPE string,
            p_key3 TYPE string.

Better: Dynamic Filter Table (For Unlimited Conditions) #

For maximum flexibility, use a table control to let users add/remove filter rows:

TYPES: BEGIN OF ty_filter,
         field TYPE fieldname,
         value TYPE string,
       END OF ty_filter.

DATA: gt_filters TYPE STANDARD TABLE OF ty_filter,
      gs_filter  TYPE ty_filter.

SELECTION-SCREEN BEGIN OF BLOCK filter_block WITH FRAME TITLE text-001.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(20) text-002. " Label: Field Name
    SELECTION-SCREEN COMMENT 25(30) text-003. " Label: Filter Value
  SELECTION-SCREEN END OF LINE.

  * Dynamic filter rows
  LOOP AT gt_filters INTO gs_filter.
    SELECTION-SCREEN BEGIN OF LINE.
      PARAMETERS: p_fld LIKE gs_filter-field MODIF ID fil.
      PARAMETERS: p_val LIKE gs_filter-value MODIF ID fil.
    SELECTION-SCREEN END OF LINE.
  ENDLOOP.

  * Button to add new filter row
  SELECTION-SCREEN PUSHBUTTON /1(12) btn_add USER-COMMAND add_filter.
SELECTION-SCREEN END OF BLOCK filter_block.

* Handle add button click
AT USER-COMMAND.
  CASE sy-ucomm.
    WHEN 'ADD_FILTER'.
      APPEND INITIAL LINE TO gt_filters.
      " Refresh selection screen to show new row
      LEAVE LIST-PROCESSING.
  ENDCASE.

2. Dynamically Build the WHERE Clause #

Next, construct your SELECT statement's WHERE condition only using the parameters the user actually filled in. This avoids empty conditions and unnecessary constraints.

For Optional Single Parameters #

DATA: lv_where_clause TYPE string,
      lt_fields       TYPE STANDARD TABLE OF fieldname,
      lt_zca_str_to_char TYPE YOUR_TABLE_TYPE. " Replace with your target structure

* Populate lt_fields with user-selected fields (e.g., p_fld1, p_fld2, p_fld3)
IF p_fld1 IS NOT INITIAL. APPEND p_fld1 TO lt_fields. ENDIF.
IF p_fld2 IS NOT INITIAL. APPEND p_fld2 TO lt_fields. ENDIF.
IF p_fld3 IS NOT INITIAL. APPEND p_fld3 TO lt_fields. ENDIF.

* Build WHERE clause
IF p_key1 IS NOT INITIAL.
  lv_where_clause = |{ lv_where_clause } AND { p_fld1 } = @p_key1|.
ENDIF.
IF p_key2 IS NOT INITIAL.
  lv_where_clause = |{ lv_where_clause } AND { p_fld2 } = @p_key2|.
ENDIF.
IF p_key3 IS NOT INITIAL.
  lv_where_clause = |{ lv_where_clause } AND { p_fld3 } = @p_key3|.
ENDIF.

* Remove leading "AND " if clause isn't empty
IF lv_where_clause IS NOT INITIAL.
  lv_where_clause = lv_where_clause+4.
ENDIF.

* Execute dynamic SELECT
SELECT (lt_fields)
  FROM (p_table)
  INTO CORRESPONDING FIELDS OF TABLE lt_zca_str_to_char
  WHERE (lv_where_clause).

For Dynamic Filter Table #

If you're using the table control approach, loop through the filled filter rows to build the clause:

DATA: lv_where_clause TYPE string.

LOOP AT gt_filters INTO gs_filter WHERE field IS NOT INITIAL AND value IS NOT INITIAL.
  IF lv_where_clause IS INITIAL.
    lv_where_clause = |{ gs_filter-field } = @gs_filter-value|.
  ELSE.
    lv_where_clause = |{ lv_where_clause } AND { gs_filter-field } = @gs_filter-value|.
  ENDIF.
ENDLOOP.

* Run the SELECT as before
SELECT (lt_fields)
  FROM (p_table)
  INTO CORRESPONDING FIELDS OF TABLE lt_zca_str_to_char
  WHERE (lv_where_clause).

3. Add Safety Checks #

Don't skip validation to avoid runtime errors or security risks:

• Validate table existence: Use DDIF_TABL_GET to confirm the user-specified table exists in the system.
• Validate fields: Use DDIF_FIELDINFO_GET to check that each selected field belongs to the target table.
• Performance warning: If no filters are provided, add a warning message about potential full-table scans (critical for large tables like EQBS).

Example validation code:

* Check if table exists
CALL FUNCTION 'DDIF_TABL_GET'
  EXPORTING
    name          = p_table
  EXCEPTIONS
    not_found     = 1
    OTHERS        = 2.
IF sy-subrc <> 0.
  MESSAGE |Table { p_table } does not exist!| TYPE 'E'.
ENDIF.

* Check if fields are valid for the table
DATA: lt_dfies TYPE STANDARD TABLE OF dfies.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
  EXPORTING
    tabname      = p_table
  TABLES
    dfies_tab    = lt_dfies
  EXCEPTIONS
    not_found    = 1
    OTHERS       = 2.
IF sy-subrc <> 0.
  MESSAGE |Failed to retrieve field info for { p_table }!| TYPE 'E'.
ENDIF.

* Validate each user-selected field
LOOP AT lt_fields INTO DATA(lv_field).
  READ TABLE lt_dfies INTO DATA(ls_dfies) WITH KEY fieldname = lv_field.
  IF sy-subrc <> 0.
    MESSAGE |Field { lv_field } is not valid for table { p_table }!| TYPE 'E'.
  ENDIF.
ENDLOOP.

4. Handle Edge Cases #

• No filters provided: The SELECT will fetch all records for the specified fields. Add a confirmation popup or warning to the user before executing this.
• Partial filter inputs: If a user enters a field name but no value, skip that condition to avoid invalid SQL.

This setup gives users the flexibility to provide 0, 1, 2, or 3 (or more, with the table control) filter parameters while maintaining the core functionality of retrieving selected fields from a target table.

内容的提问来源于stack exchange，提问作者gkubed