topic Dynamic Select in Application Development and Automation Discussions

Dynamic Select

former_member194669 — Thu, 30 Nov 2006 21:56:47 GMT

Hi All,,

I have an interntal like this:-->

data : begin of i_ytab,

matnr like mara-matnr,

pstat like mara-pstat,

mtart like mara-mtart,

matkl like mara-matkl,

bstme like mara-bstme.

data : end of o+ytab.

The internal table i_ytab have the following values

MATNR PSTAT MTART MATKL BSTME

1 P1 1A M23 M34

2 P2 2A M34

Using this i need to create dynamic select statement like the following

WHERE MATNR EQ '1' AND

PSTAT EQ 'P1' AND

MTART EQ '1A' AND

MATKL EQ 'M23' AND

BSTME EQ 'M34'.

and 2 record it should like this

WHERE MATNR EQ '2' AND

PSTAT EQ 'P2' AND

MTART EQ '2A' AND

MATKL EQ 'M34 ' AND

BSTME EQ ' '.

I like to know is there any function module to create dynamic select statments from

internal table values or from workarea values?

Thanks

aRs

Re: Dynamic Select

Former Member — Thu, 30 Nov 2006 22:10:43 GMT

Hi,

Try this..

DATA: T_WHERE(72) OCCURS 0 WITH HEADER LINE.

PERFORM WHERE_CLAUSE USING

MATNR

PSTAT

MTART

MATKL

BSTME.

SELECT * FROM MARA WHERE (T_WHERE)

INTO TABLE ITAB_MARA.

FORM WHERE_CLAUSE USING

RV_MATNR

RV_PSTAT

RV_MTART

RV_MATKL

RV_BSTME.

CLEAR: T_WHERE

1) MATERIAL

CONCATENATE ' MATNR = ' RV_MATNR INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

2) PSTAT

CONCATENATE ' AND PSTAT = ' RV_PSTAT INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

2) PSTAT

CONCATENATE ' AND PSTAT = ' RV_PSTAT INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

3) MTART

CONCATENATE ' AND MTART = ' RV_MTART INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

4) MATKL

CONCATENATE ' AND MATKL = ' RV_MAKTL INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

5) BSTME

CONCATENATE ' AND BSTME = ' RV_BSTME INTO T_WHERE.

APPEND T_WHERE.

CLEAR T_WHERE.

ENDFORM.

Thanks,

Naren

Re: Dynamic Select

Former Member — Thu, 30 Nov 2006 22:13:40 GMT

Hi aRs,

try this..

loop at i_ytab.

select single * from dtab
       WHERE MATNR EQ  I_YTAB- MATNR 
             AND PSTAT EQ  I_YTAB- PSTAT
             AND MTART EQ I_YTAB- MTART
             AND MATKL EQ I_YTAB- MATKL
             AND BSTME EQ I_YTAB-BSTME.

endloop.

also

check this dynamic selct program.

tables : pa0001, pa0002.

ranges : s_pernr for pa0001-pernr.
data : begin of i_pernr occurs 0,
pernr like pa0001-pernr,
end of i_pernr.
data : v_pernr like pa0001-pernr,
v_pernr1 like pa0001-pernr,
v_tab like DD02D-TABNAME.
data : v_field(72) type c.

data : i_source(72) occurs 0 with header line.

v_field = 'PERNR'.

v_pernr = '00000100'.
v_tab = 'PA0000'.
concatenate v_field '=' v_pernr into i_source
separated by space.

select pernr from PA0000
into corresponding fields of table i_pernr
WHERE (i_source) .


if sy-subrc <> 0.
write : / sy-subrc.

else.
delete adjacent duplicates from i_pernr comparing pernr.
loop at i_pernr.
write : / i_pernr-pernr.
endloop.

endif.

Revert back for more help

Regards

Naresh

Re: Dynamic Select

raja_thangamani — Thu, 30 Nov 2006 22:23:31 GMT

Hi,

Use the below code. It will cover all the conditions with <b>AND, OR etc</b> in the Select Query. Also the WHERE condition will be produced by SAP. So its reliable.

Populate I_SELOPT with your values as follows:

 I_SELOPT-SHLPNAME = MARA
 I_SELOPT-SHLPFIELD      = MATNR
 I_SELOPT-SIGN           = I
 I_SELOPT-OPTION         = EQ
 I_SELOPT-LOW            = 12

APPEND  I_SELOPT.

   CALL FUNCTION 'F4_CONV_SELOPT_TO_WHERECLAUSE'
      IMPORTING
        WHERE_CLAUSE = V_WHERECLAUSE
      TABLES
        SELOPT_TAB   = I_SELOPT.

I_SELOPT will have all the conditions.

Here V_WHERECLAUSE will have the dynamic where clause.

   SELECT * FROM Yourtable
             INTO table itab WHERE (V_WHERECLAUSE).

Let me know if you have any queries.

Raja T

Message was edited by:

Raja T

Re: Dynamic Select

former_member194669 — Fri, 01 Dec 2006 02:40:32 GMT

Raja,

Your suggestion worked very well.

Points awarded and thanks

aRs

Re: Dynamic Select

raja_thangamani — Fri, 01 Dec 2006 02:50:41 GMT

Hi,

If the issue is solved please close the thread.

Raja T