Skip to Content
0
Dec 17, 2020 at 09:20 AM

Dynamic SQL Error

64 Views

We have a RFC which import SQL clause and return result.

FUNCTION EXTRACT_TABLE_DATA.
*"----------------------------------------------------------------------
*"*"import
*"  TABLES
*"      FIELDS STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      FROMCLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      WHERECLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      DATA STRUCTURE  ZTABLEROWS
*"----------------------------------------------------------------------
  TYPE-POOLS: abap.
  DATA:
        columnName TYPE SO_TEXT,
        fieldDataDescrRef  TYPE REF TO abap_componentdescr,
        numberFields TYPE i,
        fieldDescr TYPE abap_componentdescr,
        fieldname TYPE string,
        fieldDescrTab TYPE abap_component_tab,
        rowStructDescr TYPE REF TO cl_abap_structdescr,
        rowReference TYPE REF TO data,
        returnRowString TYPE string,
        dataFieldString TYPE string,
        dataline LIKE data,
        fromClauseRow TYPE ZSQL_CLAUSE_ELEMENTS,
        fromClauseString TYPE string,
        whereClauseRow TYPE ZSQL_CLAUSE_ELEMENTS,
        whereClauseString TYPE string,
        fieldsRow TYPE ZSQL_CLAUSE_ELEMENTS,
        FNAME(600) VALUE 'myfile'.

  FIELD-SYMBOLS:
          <datarow> TYPE ANY,
          <datafield> TYPE ANY.


* CREATE DataStructure with field names
* Datatypes are read from fieldnames of FIELDS input table
  DESCRIBE TABLE FIELDS LINES numberFields.
  LOOP AT FIELDS INTO fieldsRow.
    fieldname = SY-TABIX.
* names need to be unique and must start with a char
    CONCATENATE 'string' fieldname INTO fieldname.
    CONDENSE fieldname.
    fieldDescr-name = fieldname.
* for dictionary lookup we need to change columnnames from Open SQL
* to dictionary notation
    columnName = fieldsRow-TEXT.
    REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE.

    fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ).
    APPEND fieldDescr TO fieldDescrTab.
  ENDLOOP.

  rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).

* now we create the actual data structure in memory
  create data rowReference type HANDLE rowStructDescr.
* finally we assign it to the Field-symbol used by the select statement
  ASSIGN rowReference->* TO <datarow>.
* End Create DataStructure

* to simplify calls we concatenate from and whereclause into strings
* this way caller doesn't need to check word wrappings
  fromClauseString = ''.
  LOOP AT FROMCLAUSE INTO fromClauseRow.
    CONCATENATE fromClauseString fromClauseRow-TEXT INTO fromClauseString.
  ENDLOOP.

  whereClauseString = ''.
  LOOP AT WHERECLAUSE INTO whereClauseRow.
    CONCATENATE whereClauseString whereClauseRow-TEXT INTO whereClauseString.
  ENDLOOP.
*IF sy-uname = 'SIENIT01'.
* break-point.
*ENDIF.
* Now start actual select operation
  SELECT (FIELDS) FROM (fromClauseString) INTO @<datarow> WHERE (whereClauseString).
* we read all fields of the current row, cast it to string and
* concatenate it into a dataline with division chars.
    CLEAR: returnRowString.
    DO numberFields TIMES.
      ASSIGN component sy-index of structure <datarow> to <datafield>.
      dataFieldString = <datafield>.
      CONCATENATE returnRowString '^' datafieldstring INTO returnRowString.
    ENDDO.
    dataline = returnRowString.
* finally dataline is added to the return table.
    INSERT dataline INTO TABLE data.
  ENDSELECT.
open DATASET FNAME FOR APPENDING IN TEXT MODE ENCODING DEFAULT.
TRANSFER whereClauseString TO FNAME.
IF SY-SUBRC <> 0.
  WRITE: 'SY-SUBRC:', SY-SUBRC,
       / 'System Message:'.
ENDIF.
CLOSE DATASET FNAME.
ENDFUNCTION.

The four table parameters fields,fromclause,whereclause,data are all table of string.

I use the following parameter set and it runs ok.

fields: ekko~ebeln
fromclause: ekko 
whereclause: 
data:

But I add another row in fields table and it has error.

fields: ekko~ebeln 
        ekko~lifnr
fromclause: ekko
whereclause: 
data:

The error is in SQL clause. I checked the fields ebeln and lifnr are in the ekko table. So what's the correct way to use dynamic fields in select? Thx.