Dynamic WHERE Clause Specification

[Former Member]

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

[Former Member]

Have you debugged to see the contents of itab?

Rob

[Former Member]

Rodolfo, Check this link,

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

Its similar to your requirment.

[Former Member]

Hi Federico,

Try removing the '.' in this line:

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

Regards,

Karl

[former_member346628]

Hi,

     Please try this may be this is helpfull.

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

[Clemenss]

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

Regards,

Clemens

[Former Member]

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.

[Former Member]

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.

[Former Member]

Hello,

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

Regards

[laurent_fournier2]

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.

[Former Member]

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.

[Former Member]

i did this already but it doesnt worked

[Former Member]

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:

[Former Member]

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

[Former Member]

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.

[Former Member]

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

Rob