topic Re: Dynamic WHERE Clause Specification in Application Development and Automation Discussions

Dynamic WHERE Clause Specification

Former Member — Mon, 14 May 2012 16:19:50 GMT

Hello,

i am triying to implement function that uses a dynamic Select query with open sql. Here is what i have.

Parameters:

*"     REFERENCE(DATEFROM) TYPE DATS

*"     REFERENCE(P_DYPROFELD) TYPE DATA
*"     REFERENCE(DB_TABELLE) TYPE STRING

*"     REFERENCE(P_TABELLENFELD) TYPE STRING

...

DATA: cond(72) TYPE c, strtest TYPE string VALUE 'dateto ',

strtest2 TYPE string VALUE '99991231',

itab LIKE TABLE OF cond.

...

(1) CONCATENATE p_tabellenfeld '=' p_dynprofeld 'and' INTO cond SEPARATED BY space.

(2) APPEND cond TO itab.

CONCATENATE strtest ' = ' strtest2 '.' INTO cond SEPARATED BY space.

APPEND cond TO itab.

SELECT * FROM (db_tabelle) INTO <row>

(3) WHERE (itab) .

...

the problem is with (itab). I get a CX_SY_DYNAMIC_OSQL_SEMANTIC expcetion with the runtime error SAPSQL_WHERE_ILEGAL_VALUE

i think the problem is the cast from ANY to String in the concatenate command (1). Because when I comment (1) and (2) and wirte in (3)

WHERE (itab) and dexproc = p_dynprofeld it works. Any ideas on what i am doing wrong get this exception or how could i cast a data to string correctly.

Regards,

Rodolfo

Re: Dynamic WHERE Clause Specification

Former Member — Mon, 14 May 2012 16:35:43 GMT

Have you debugged to see the contents of itab?

Rob

Re: Dynamic WHERE Clause Specification

Former Member — Mon, 14 May 2012 16:38:27 GMT

Rodolfo, Check this link,

http://wiki.sdn.sap.com/wiki/display/ABAP/Dynamic+where+clause

Its similar to your requirment.

Re: Dynamic WHERE Clause Specification

Former Member — Mon, 14 May 2012 17:50:11 GMT

Hi Federico,

Try removing the '.' in this line:

CONCATENATE strtest ' = ' strtest2 '.' INTO cond SEPARATED BY space.

Regards,

Karl

Re: Dynamic WHERE Clause Specification

former_member346628 — Mon, 14 May 2012 18:04:10 GMT

Hi,

Please try this may be this is helpfull.

http://scn.sap.com/thread/734719.

Re: Dynamic WHERE Clause Specification

Clemenss — Mon, 14 May 2012 20:15:49 GMT

Try 'AND' not 'and'. 'DATETO' for 'dateto' - everything capitalized. And make sure the dbtabelle has fields of exactly that names.

Regards,

Clemens

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 15 May 2012 04:15:40 GMT

Hi,

As far as I know this error occurs when your dynamic WHERE clause contains invalid value.

I suppose that when you execute your report you left the p_dynprofeld empty. Allowing the word AND be the value for p_tabellenfeld. So, it will look like this:

 p_tabellenfeld = AND.

Regards,

Jake.

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 15 May 2012 04:39:58 GMT

Hello,

include capital letters between quotes( i.e.' A' )

Regards

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 15 May 2012 05:06:00 GMT

So, switch the position of the value of your WHERE claue. Try this:

(1) CONCATENATE  strtest ' = ' strtest2 'and' INTO cond SEPARATED BY space.

  APPEND cond TO itab.
(2)  CONCATENATE  p_tabellenfeld  '='  p_dynprofeld INTO cond SEPARATED BY space.
  APPEND cond TO itab.
SELECT * FROM (db_tabelle) INTO <row>

          (3)  WHERE (itab) .

Also, make sure that you provide the value of db_tabelle.

Jake.

Re: Dynamic WHERE Clause Specification

laurent_fournier2 — Tue, 15 May 2012 07:26:25 GMT

Hello,

Have a look in this code.

loop at input.

perform check_entry using 'T001' "Tabname

'BUKRS' "Where Field 1

input-bukrs "Where Value 1

space "Where Field 2

space "Where Value 2

space "Where Field 3

space "Where Value 3

space "Where Field 4

space "Where Value 4

space "Where Field 5

space "Where Value 5

sy-tabix "File Line ( Error Log)

input-bukrs "Checked Value

'BUKRS'. "Checked Field

endloop.

FORM check_entry USING u_tabname

u_field1

u_value1

u_field2

u_value2

u_field3

u_value3

u_field4

u_value4

u_field5

u_value5

u_line

U_search_value

u_fieldname.

DATA : STR_LINE TYPE EDPLINE,

STR_WHERE TYPE TABLE OF EDPLINE.

data : w_and(3) type c.

clear : w_and.

if u_field2 >< space.

w_and = 'AND'.

endif.

perform add_where_field using u_field1

u_value1

w_and

changing str_line.

append : str_line to str_where.

clear : w_and.

if u_field3 >< space.

w_and = 'AND'.

endif.

perform add_where_field using u_field2

u_value2

w_and

changing str_line.

append : str_line to str_where.

clear : w_and.

if u_field4 >< space.

w_and = 'AND'.

endif.

perform add_where_field using u_field3

u_value3

w_and

changing str_line.

append : str_line to str_where.

clear : w_and.

if u_field5 >< space.

w_and = 'AND'.

endif.

perform add_where_field using u_field4

u_value4

w_and

changing str_line.

append : str_line to str_where.

clear : w_and.

perform add_where_field using u_field5

u_value5

w_and

changing str_line.

append : str_line to str_where.

SELECT count(*) FROM (u_tabname)

WHERE (str_where).

if sy-subrc >< 0.

perform error_insert using u_line

u_fieldname

'E'

'Value'(e01)

u_search_value

'Does Not Exist In System'(e02)

space.

endif.

ENDFORM. " check_entry

*&---------------------------------------------------------------------

*& Form add_where_field

*&---------------------------------------------------------------------

* text

*----------------------------------------------------------------------

* -->P_U_FIELD1 text

* -->P_U_VALUE2 text

* -->P_SPACE text

* <--P_STR_LINE text

*----------------------------------------------------------------------

FORM add_where_field USING U_FIELD

U_VALUE

u_and

CHANGING c_where.

check not : u_field is initial.

data : w_value(50) type c.

clear : c_where.

clear : w_value.

concatenate : ''''

u_value

''''

into w_value.

concatenate : u_field

'='

w_value

u_and

into

c_where

separated by space.

ENDFORM. " add_where_field

Regards.

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 15 May 2012 20:57:33 GMT

Hi,

Can't you just use another string variable to hold that P_DYPROFELD?

DATA: l_dyprofeld TYPE string.

l_dyprofeld = P_DYPROFELD.

CONCATENATE p_tabellenfeld '=' l_dyprofeld 'and' INTO cond SEPARATED BY space.

Kr,

Manu.

Re: Dynamic WHERE Clause Specification

Former Member — Wed, 16 May 2012 11:07:14 GMT

FM: 'RSDS_RANGE_TO_WHERE use to exports the whole SQL string for the WHERE clause as a STRING to parameter e_where but you have to use a (generic) range table structure of type type RS_T_RSCEDST.

Example:

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 22 May 2012 07:52:34 GMT

Hello thanks everyone for your helpful answers the Problem quotation marks.It was on the following line

CONCATENATE p_tabellenfeld '=' p_dynprofeld 'and' INTO cond SEPARATED BY space.

and it should be

CONCATENATE p_tabellenfeld '=' ' ' p_dyprofeld ' ' ' ' INTO cond SEPARATED BY space.

the content of the variable p_dyprofeld should be in ' ' to be interpreted in where clausel. When not you get a runtime error.

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 22 May 2012 07:54:06 GMT

i did this already but it doesnt worked

Re: Dynamic WHERE Clause Specification

Former Member — Tue, 22 May 2012 07:56:39 GMT

This is insteresting can you post your example again, beacuse it wont be showed.

Re: Dynamic WHERE Clause Specification

Former Member — Wed, 23 May 2012 13:40:14 GMT

Would you not have seen that a week ago if you had taken the first suggestion you got?

Rob