topic Dynamic SQL Selection in Application Development and Automation Discussions

Dynamic SQL Selection

john_wayne — Wed, 03 Aug 2011 14:39:06 GMT

im getting this error SAPSQL_IN_ILLEGAL_LIST when i do my dynamic selection on the where clause. Im running on 4.6C and the dynamic selection is like this

lv_select = 'PERNR IN SO_PERNR AND BEGDA IN SO_BEGDA AND ENDDA IN SO_ENDDA'

SELECT (lv_select_fields) FROM (lv_table) INTO <fs> WHERE (lv_select).

Is it because i used the in structure in select?

is there a solution to work around this issue?

Re: Dynamic SQL Selection

former_member209703 — Wed, 03 Aug 2011 15:07:31 GMT

Maybe the error is linked to the Input List.

Have you tried using SELECT * instead?


TABLES: PA0001.
*
select-options: so_pernr for pa0001-pernr,
                so_begda for pa0001-begda,
                so_endda for pa0001-endda.

data: where_tab type standard table of char100,
      where_clause(100) type c.
DATA: dataref TYPE REF TO data.
FIELD-SYMBOLS: <FS>, <CAMPO>.

DATA: L_TABLA(30).

L_TABLA = 'PA0001'.

CREATE DATA dataref TYPE (L_TABLA).
ASSIGN dataref->* TO  <FS>.


WHERE_CLAUSE =  'PERNR IN SO_PERNR AND'.
APPEND WHERE_CLAUSE TO WHERE_TAB.

WHERE_CLAUSE =  'BEGDA IN SO_BEGDA AND'.
APPEND WHERE_CLAUSE TO WHERE_TAB.

WHERE_CLAUSE =  'PERNR IN SO_ENDDA'.
APPEND WHERE_CLAUSE TO WHERE_TAB.


  select *
         from (L_TABLA)
         INTO  <FS>
         where (WHERE_TAB).
 endselect.

Re: Dynamic SQL Selection

john_wayne — Wed, 03 Aug 2011 15:30:31 GMT

im actually not allowed to use select endselect but the error is coming from the where clause or the select fields part?

Re: Dynamic SQL Selection

Sandra_Rossi — Wed, 03 Aug 2011 15:49:42 GMT

Hi Abaperdaft,

The documentation (http://help.sap.com/saphelp_46c/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/frameset.htm) clearly says:


SELECT ... WHERE (<itab>) ...
where <itab> is an internal table with line type C and maximum length 72 characters. All of the conditions listed above except for selection tables , can be written into the lines of <itab>. However, you may only use literals, and not the names of data objects . The internal table can also be left empty.
If you only want to specify a part of the condition dynamically, use:
SELECT ... WHERE <cond> AND (<itab>) ...
You cannot link a static and a dynamic condition using OR.
You may only use dynamic conditions in the WHERE clause of the SELECT statement.

So, you cannot use IN, and you cannot indicate a data object.

To convert a range into WHERE, you should call function module FREE_SELECTIONS_RANGE_2_WHERE. I think there are examples in SDN. If it's not clear how to make it work, please revert back to us.

Sandra

Re: Dynamic SQL Selection

former_member209703 — Wed, 03 Aug 2011 15:53:10 GMT

If you are not allowed to use SELECT/ENDSELECT, you may use SELECT.. INTO TABLE instead

In this case you can use the SELECT-OPTIONS as part of the condiition tab, as shown above.