Skip to Content
author's profile photo Former Member
Former Member

how to build the where clause dynamically

Hi all,

Currently i am trying a search help functionality using search help exit.

in this functionality if a user gives a two word input then the two words should be linked with

AND condition.

for ex if input for address is nehru street then the where clause to be used should be like this

WHERE address cp nehru AND address cp street.

similarly if the user give N words as input then the N words should be linked with AND condition

and the where clause should be

WHERE address cp word1 AND address cp word2 AND address cp word3 AND address cp wordN.

i tried by replacing the space with * but it will work only when the order of the words in the field

is same as that of input.

ex: word1 word2 word3 word4 replaced by word1*word2*word3*word4.

but if the order of words in input is changed (ex: word3 word1 word4 word2) it wont work.

but still i have to retrieve the data (word1 word2 word3 word4) even if the input is in any order (word3 word1 word4 word2).

how this can be done.

i am going to use this where clause in the Loop at statement.

your suggestions will be really helpful to me.

Thanks and Regards,

Mohanarangan.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Jan 09, 2013 at 07:22 PM

    Hi Mohanarangan,

    You can build a dynamic where clause for a LOOP as follows...

    DATA lv_condition TYPE string.lv_condition = 'field1 CP pattern AND field2 = value'.LOOP AT itab ASSIGNING <fs>                       WHERE (lv_condition).      ...ENDLOOP.

    Take a look at the ABAP keyword documentation for LOOP AT itab - cond for more details.

    The SEPARATED BY addition to the CONCATENATE keyword may also be of interest to you...

    CONCATENATE x y z INTO myvar SEPARATED BY space.

    Cheers,

    Amy

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 09, 2013 at 07:18 PM

    Use the IN operator of ABAP SQL

    In this example we want to filter by ASSET Code so, i create a DATA TYPE in our program like this:

    types:     begin of p_anln1,             sign(1),             option(2),             low(12),             high(12),         end of p_anln1 .

    then we create a variable

    data it_anln1 type standard table of  p_anln1 with header line. "- Internar table of the type we just create

    So, we can fill this variable like this:

    clear it_anln1.it_anln1-sign = 'I'.it_anln1-option = 'EQ'.it_anln1-low = '00001'append it_anln1.clear it_anln1.it_anln1-sign = 'I'.it_anln1-option = 'EQ'.it_anln1-low = '00002'append it_anln1 .clear it_anln1.it_anln1-sign = 'I'.it_anln1-option = 'EQ'.it_anln1-low = '00003'append it_anln1.

    Then we do the select

    Select * from anlninto IT_TABLEwhere anln1 in it_anln1.

    DONE!

    This is just as we do a select like:

    Select * from anlninto IT_TABLEwhere anln1 = '00001'and anln1 = '00002'and anln1 = '00003'
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Marcos,

      Thanks for your reply.

      but the thing is i am going to use the where clause in LOOP AT statement not in SELECT statement.

      the next thing is i am going to search for the data which contains all the words given in the input parameter.

      for example: if the input for address field is #10 nehru street.then the loop should process only the data which contains

      all these three words.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2013 at 07:21 PM

    i tried another option of building the where clause dynamically by.using the sample code below

    tables mara.

    parameters: word(60) type c.

    data: begin of itab occurs 0,

    word (60) type c,

    end of itab.

    data: inttab type table of mara with header line.

    select * from mara into table inttab.

    while word contains space.

    split word at ' ' in to word itab-word.

    append itab.

    if word co space.

    exit.

    endif.

    endwhile.

    loop at itab.

    if sy-tabix =1.

    concatenate 'word' '#' 'cp' '#' '*' itab-word.into word.

    else.

    concatenate word '#' 'AND' '#' 'word' '#' 'cp' '#' itab-word into word.

    endloop.

    while word ca '#'.

    replace '#' with ' ' in word.

    endwhile.

    loop at inttable where ('word').

    but this attempt of using the dynamic where clause also got failed 😔 with syntax error in the loop at statement.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 09, 2013 at 07:32 PM

    Steps

    • split the input text at space into a table of words
    SPLIT text AT ` ` INTO TABLE itab
    • build a range table (TYPE RANGE OF xxx)
    LOOP AT itab INTO word.  CLEAR range.  range-sign = 'I'.  range-option = 'CP'.  CONCATENATE '*' word '*' INTO range-low.  APPEND range TO range_tab.ENDLOOP.
    • Use the range in the SELECT statement
    SELECT ... WHERE field IN range_tab.

    Regards,

    Raymond

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2013 at 05:13 AM

    If you want to use your approach you need to formulate the query like this:

    Say your Internal table(itab) has 3 cols: (col1, col2, col3)

    SPLIT lv_search_String AT space INTO itab_str.

    LOOP AT itab_str INTO lv_str.

    IF lv_condition IS INITIAL.

    lv_condition = 'col1 CP lv_str OR col2 CP lv_str OR col3 CP lv_str'.

    ELSE.

    CONCATENATE lv_condition 'AND' INTO lv_condition SEPERATED BY space.

    CONCATENATE lv_condition 'col1 CP lv_str OR col2 CP lv_str OR col3 CP lv_str' INTO lv_condition SEPEARTED BY space.

    ENDLOOP.

    LOOP AT Itab INTO ls_itab WHERE (lv_condition).

    ...

    ENDLOOP.

    However this is an another approach.

    SPLIT lv_search_string AT space INTO itab_str.

    DESCRIBE TABLE itab_str LINE lines.

    lv_index = 0.

    LOOP AT itab INTO ls_itab.

    CONCATENATE ls_itab-col1 ls_itab-col2 ls_itab-col3 INTO lv_string SEPEARTED BY space.

    DO lines TIMES.

    lv_index = lv_index + 1.

    READ TABLE itab_str INTO lv_str INDEX lv_index.

    IF sy-subrc EQ 0.

    FIND FIRST OCCURENCE OF lv_str IN lv_string.

    IF sy-subrc EQ 0.

    lv_continue = abap_true.

    ELSE.

    lv_continue = abap_false.

    ENDIF.

    ENDIF.

    ENDDO.

    IF lv_continue = abap_true.

    " This means that all the search string words are found in this row of your internal table

    ELSE.

    " This means that all the search string words are not found in this row of internal table

    ENDIF.

    ENDLOOP.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.