topic Dynamic Where clause usage in Application Development and Automation Discussions

Dynamic Where clause usage

saptarshi_sen — Fri, 22 Jun 2012 07:42:36 GMT

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.

Re: Dynamic Where clause usage

Kartik2 — Fri, 22 Jun 2012 07:47:39 GMT

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

Re: Dynamic Where clause usage

RaymondGiuseppi — Fri, 22 Jun 2012 08:06:08 GMT

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

Re: Dynamic Where clause usage

lijisusan_mathews — Fri, 22 Jun 2012 08:10:43 GMT

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

Re: Dynamic Where clause usage

saptarshi_sen — Sat, 23 Jun 2012 05:39:21 GMT

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.

Re: Dynamic Where clause usage

Former Member — Wed, 27 Jun 2012 07:11:15 GMT

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

Re: Dynamic Where clause usage

saptarshi_sen — Fri, 29 Jun 2012 13:19:29 GMT

Thanks Simo, this seems to suit the requirement..