topic Re: Dyanamic Select Statements in Application Development and Automation Discussions

Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:25:02 GMT

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.

Re: Dyanamic Select Statements

RichHeilman — Thu, 19 Jan 2006 14:31:53 GMT

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

Re: Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:34:29 GMT

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

Re: Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:34:52 GMT

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-9989fd650822#q-16

As far as your code is concerned it looks Ok.

Lanka

Re: Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:36:09 GMT

Hi,

I executed your code in my system it is working

fine .

Regards,

Amole

Re: Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:36:58 GMT

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

Re: Dyanamic Select Statements

RichHeilman — Thu, 19 Jan 2006 14:39:52 GMT

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

Re: Dyanamic Select Statements

Former Member — Thu, 19 Jan 2006 14:47:16 GMT

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.