topic Re: Dynamic where clause problem? in Application Development and Automation Discussions

Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:03:10 GMT

Hi all,

I am using 4 parameters for the attrition rate report of an employee on the selection screen.

Level

Grade

Position

Salary Range (pa0008-ansal)

i have filled the values of level, grade, position, from database tables at the initialization event.

And for the salary range i hardcoded them to 0-5000, 5001-10000 etc.. and so on.

and then showed all 4 parameters are listbox on the selection screen.

now the first 3 parameters are from infotype pa0001 and the last is from pa0008.

Now I want to select pernr, persg ,persk ,plans, begda ,endda fields from infotype pa0001, and 'ansal' field from pa0008 , with the help of a dynamic where clause, is it possible, because here it might be needed for a join as well.

Please help me out with some sample coding help.

Regards

Tarun

Re: Dynamic where clause problem?

nabheetscn — Wed, 28 Dec 2011 08:05:26 GMT

Try to use for all entries or join mate.

Nabheet

Re: Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:07:13 GMT

hi nabheet,

I am using the join, but i am having some problem making the dynamic where clause, can u help out?

Re: Dynamic where clause problem?

nabheetscn — Wed, 28 Dec 2011 08:09:23 GMT

Can you please pasted code with problem

Nabheet

Re: Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:11:42 GMT

DATA: lv_query TYPE string.
  lv_query = ' '.
  IF  position IS NOT INITIAL.
    IF lv_query IS NOT INITIAL.
      CONCATENATE lv_query '  and p1~plans eq   position '  '' INTO lv_query.
    ELSE.
      CONCATENATE  ' p1~plans eq  position '  '' INTO lv_query.
    ENDIF.
  ENDIF.

  IF  grade IS NOT INITIAL.
    IF lv_query IS NOT INITIAL .
      CONCATENATE lv_query '  and p1~PERSK eq  grade ' '' INTO lv_query.
    ELSE.
      CONCATENATE '  p1~PERSK eq  grade  ' '' INTO lv_query.
    ENDIF.
  ENDIF.

  IF  level IS NOT INITIAL.

    IF lv_query IS NOT INITIAL .
      CONCATENATE lv_query '  and p1~PERsg eq  level ' '' INTO lv_query.
    ELSE.
      CONCATENATE ' p1~PERsg eq  level  ' '' INTO lv_query.
    ENDIF.
  ENDIF.
*
  IF  sal_slab IS NOT INITIAL.
    SELECT pernr ansal begda endda FROM pa0008 INTO CORRESPONDING FIELDS OF TABLE lt_st_p8 WHERE ansal = sal_slab.

    *IF lv_query IS NOT INITIAL .*
      *CONCATENATE lv_query '  and p8~ansal le  sal_slab ' '' INTO lv_query.*
    *ELSE.*
      *CONCATENATE '  p8~ansal le  sal_slab  ' '' INTO lv_query.*
    *ENDIF.*
  ENDIF.

SELECT p1~pernr
    p1~persg p1~persk p1~plans p1~begda p1~endda
     p8~ansal
  INTO CORRESPONDING FIELDS of TABLE lt_st_p1_p8
    FROM pa0001 AS p1 JOIN pa0008 AS p8  ON p1~pernr = p8~pernr
    WHERE (lv_query) .

<Added code tags>

Now in case of salary slab, i have ranges like 0-5000 , 5001-10000 and so on and I showed it as a listbox on sel. screen.

So how can I read the figure after the '-'?????

And also review, if for the previous 3 parameters it is correct, or it can be shortened and accurated.

Edited by: Suhas Saha on Dec 28, 2011 1:44 PM

Re: Dynamic where clause problem?

nabheetscn — Wed, 28 Dec 2011 08:22:39 GMT

what you can do is you can declare a range variable

data: lr_sal_slab type range of <data type>,
lwa_sal_slab like line of lr_sal_slab.,

Now based on value selected in drop down
fill this range as
lwa_sal_slab-sign = 'I'.
lwa_sal_slab-option = 'EQ'.
lwa_sal_slab-low = lower limit.
lwa_sal_slab-high = upper limit.
append lwa_sla_slab to lr_sal_slab.

if lr_sal_slab[] is not initial.

lwa_sal_slab-sign = 'I'.
CONCATENATE lv_query '  and p8~ansal in  lr_sal_slab ' '' INTO lv_query.
endif.

Nabheet

Re: Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:28:45 GMT

ya that is one option, but in this case, i will have to case on the salary range parameter.

So there will be about 12 cases.

Cant i use some SPLIT statement, to split the selected value when it reaches '-'.

Like i am have ranges like 0-5000

5001- 10000

...........

100001&above.

So can i somehow split them, and then use p8~ansal bet... result1 and result 2.

I am not exactly getting that. Can u help me out????????

Re: Dynamic where clause problem?

nabheetscn — Wed, 28 Dec 2011 08:34:22 GMT

Yes you can use split at '-' statment and put it into two variables and use between.

try it

Nabheet

Re: Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:41:25 GMT

ya that will work out!

But what about the last case, 100001 & above, in this case,

there is no '-', so how will i do that???

data sal_slab_low(7) type c.

data sal_slab_high(7) type c.

READ TABLE list_sal_slab into value_sal_slab with key key = sal_slab.

SPLIT value_sal_slab-text at '-' into sal_slab_low sal_slab_high.

IF sal_slab IS NOT INITIAL.

SELECT pernr ansal begda endda FROM pa0008 INTO CORRESPONDING FIELDS OF TABLE lt_st_p8 WHERE ansal = sal_slab.

IF lv_query IS NOT INITIAL .

CONCATENATE lv_query ' and p8~ansal ge sal_slab_low and le sal_slab_high ' '' INTO lv_query.

ELSE.

CONCATENATE ' p8~ansal ge sal_slab_low and le sal_slab_high ' '' INTO lv_query.

ENDIF.

This is working, but for the last case, how can I implement?

Re: Dynamic where clause problem?

nabheetscn — Wed, 28 Dec 2011 08:45:20 GMT

You will have value in first variable not in second implement check for that case if

split lv_string at '-' intolv_val1 lv_val2.

if lv_val2 is initlal

do not use

else.

use

Nabheet

Re: Dynamic where clause problem?

Former Member — Wed, 28 Dec 2011 08:50:41 GMT

ya thanks nabheet. Problem solved.