Dyanamic Select Statements

[Former Member]

Hi ,

The following program gives a short dump with exception CX_SY_DYNAMIC_OSQL_SYNTAX. Its ok when we use paremeters in the Dyanamic statements . Please highlight on this regard.

Thanks in advance,

Raj

REPORT ZMT_DYNAMIC_SELECT .

tables:mara.

select-options:p_matnr for mara-matnr default '38'.

data:lv_text(40).

data:itab type mara occurs 0 with header line.

concatenate 'matnr' 'in' 'p_matnr' into lv_text separated by space .

translate lv_text to upper case.

select * from mara into table itab where (lv_text).

Loop at itab.

write:/ itab-matnr.

check sy-tabix eq 5.

exit.

endloop.

[RichHeilman]

Please try this.

report zmt_dynamic_select .

tables:mara.
select-options:p_matnr for mara-matnr default '38'.

<b>data:lv_text(40) type c occurs 0 with header line.</b>

data:itab type mara occurs 0 with header line.

concatenate 'matnr' 'in' 'p_matnr' into lv_text separated by space .
translate lv_text to upper case.
<b>append lv_text.</b>


select * from mara into table itab where (lv_text).


loop at itab.

  write:/ itab-matnr.


  check sy-tabix eq 5.


  exit.



endloop.

The problem here is that the WHERE clause should be an internal table. And of course you must append to it.

Regards,

Rich Heilman

[Former Member]

Make the following change

Sorry thought it is a parameter, you can not use IN operator in dynamic select where clause. It is a restriction.

Regards,

Srikanth

Message was edited by: Srikanth Lodd

[Former Member]

Hi Raja,

Please review the code provided in SDN for Dynamic Select statements :

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/840ad679-0601-0010-cd8e-998...

As far as your code is concerned it looks Ok.

Lanka

[Former Member]

Hi,

I executed your code in my system it is working

fine .

Regards,

Amole

[Former Member]

Hi Raja,

<b>

You cannot use Select options with 'IN' in dynamic where clause.</b>

System can only recognize literals and not variables in dynamic WHERE condition.

Hope this helps..

Sri

[RichHeilman]

Also, I believe you must use literals, no variables in your dynamic where clause.

report zmt_dynamic_select .

tables:mara.
*select-options:p_matnr for mara-matnr default '40000100'.

data:lv_text(40) type c occurs 0 with header line.

data:itab type mara occurs 0 with header line.

concatenate 'matnr' '=' <b>'000000000040000100'</b> into lv_text separated by
space .
translate lv_text to upper case.
append lv_text.


select * from mara into table itab where (lv_text).


loop at itab.

  write:/ itab-matnr.


  check sy-tabix eq 5.


  exit.



endloop.

Regards,

Rich Heilman

[Former Member]

Hi Raja,

1. Before trying out any dynamic sql,

first try with hardcoded sql.

2. where (lv_text).

this lv_text is causing the problem.

3. Since, fieldname = 'value'

this is the syntax for sql.

4. Hence, SINGLE QUOTES are important for

CHARACTER Fields

and NUMERIC fields don't require

single quotes.

DATE require single quotes

in the format YYYYMMDD

5. Hence, the problem is only in the

syntax of dynamic sql.

regards,

amit m.