Dynamic Where clause usage

[saptarshi_sen]

Hello all,

I have a requirement where i need to create an FM which basically serves as a search tool. I have 5 input parameters (say EmployeeID ,Name, Address, Phone etc) and will return the same parameters (Result EmployeeID ,Name, Address, Phone) as export. When i give any one input parameter at a time, it is working fine, as i have coded individual blocks for each parameter. However, i need to have a situation where one may input any one or more input parameters
(say Name and Phone), and need to return the resulting employee(Also incase Employee/Name and phone are not matching, need to return 0/No values).Guess this would need a dynamic select query. Could somebody help me implementing the same, with a similar scenario ?

Thanks in advance!

Saptarshi.

[Kartik2]

Hi,

Are you selecting all the required data from a single table. If yes, then you can make use of ranges. Based on the input provided by the user to the function module. build your ranges internal table and then use these ranges in select query, in the same way as you use select option with IN operator.

Hope it helps you. Thank you.

Regards,

Kartik

[saptarshi_sen]

Thanks for the response Kartik ..

I tried using Ranges as well, although in my case i am fetching data from 4 different tables (customer master tables),  and things are also working fine on giving one input parameter..

But things are getting complicated when i'm putting in two different parameters, as i am using around 9 input parameters.. Would that need me to define a set of range tables for each parameter block ?

Somehow feel that it's not as complicated as i think, and there's a simpler way out ..

Regards,

Saptarshi.

[RaymondGiuseppi]

You could build a dynamic where clause or convert parameter to ranges like in following sample :

  DATA: s_kunnr TYPE RANGE OF kna1-kunnr WITH HEADER LINE,
        s_name1 TYPE RANGE OF kna1-name1 WITH HEADER LINE.
  DEFINE range_build.
    refresh s_&1.
    if &1 is not initial.
      s_&1-sign = 'I'.
      if &1 CA '*+'.
        s_&1-option = 'CP'.
      else.
        s_&1-option = 'EQ'.
      endif.
      s_&1-low = &1.
      append s_&1.
    endif.
  END-OF-DEFINITION.
  range_build kunnr.
  range_build name1.
  SELECT SINGLE kunnr name1 INTO (kunnr, name1)
    FROM kna1
    WHERE kunnr IN s_kunnr
      AND name1 IN s_name1.
  IF sy-subrc NE 0.
    RAISE not_found.
  ENDIF. 

Regards,

Raymond

[lijisusan_mathews]

You can use select-options or Ranges to fetch data from your table, if its available in a single table.

Alternately, if you know the statement in each case, and teh cases are also well defined, you can store your dynamic where conditions in a variable and use it in teh select.

For eg.

If p_1 = 'X'.

     w_dynamic = ' xref1 = 'Y' '.

elseif p_1 = space.

     w_dynamic = ' xref1 = space '.

else.

     w_dynamic = ' xref1 <> space '.

endif.

Select * from bkpf where

             belnr in so_belnr and

             gjahr in so_gjahr and

             ( w_dynamic ).

Suzie

[Former Member]

You can also use combination of ranges and dynamic sql.

DATA: l_condition TYPE string.

DATA: l_table TYPE string.

DATA: p_matnr TYPE RANGE OF ekpo-matnr.

DATA: p_matnr_ld LIKE LINE OF p_matnr.

p_matnr_ld-sign = 'I'.
p_matnr_ld-low = '0000000012345'.
p_matnr_ld-option = 'EQ'.
APPEND p_matnr_ld TO p_matnr.

MOVE 'ekpo' TO l_table.

CONCATENATE l_table  '~matnr IN p_matnr' INTO l_condition.

SELECT *

      FROM (l_table)

         INTO  TABLE lt_xxxx

          WHERE (l_condition)

Simo

[saptarshi_sen]

Thanks Simo, this seems to suit the requirement..