topic Dynamic Where clause in Application Development and Automation Discussions

Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:04:09 GMT

Hey Guys,

I want to create the dynamic where clause.

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

SELECT *

INTO TABLE GI_VBAK

FROM VBAK

WHERE (GI_WHERE).

In that where clause contain ranges.

for eg.

GI_WHERE = 'VBELN IN S_VBELN'.

Thanks,

Suresh.

Message was edited by: Suresh Kumar

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:06:46 GMT

No! U can't do it, but only insert constant values in WHERE table:

DATA: FIELD(30).

SELECT *

INTO TABLE GI_VBAK

FROM VBAK

WHERE (GI_WHERE).

FIELD = 'VBELN ='.

CONCATENATE FIELD '''0000000001''' INTO GI_WHERE.

APPEND GI_WHERE.

Max

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:08:32 GMT

Hey max bianchi,

Then what is the solution for that?

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:10:20 GMT

U should convert the SELECT-OPTIONS.

But I should know what you need to do exactly

Max

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:17:05 GMT

Hey max bianchi,

GI_WHERE = 'VBELN IN S_VBELN'.

Here s_vbeln is a select-options only.

That have maltiple values.

in select statement i want to pass the list of values in dynamiclly.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:19:13 GMT

Ok! I understand but why do you need to do that?

Why don't you write simple

SELECT * FROM VBELN IN S_VBELN ?

Max

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:22:14 GMT

Yes...You can use a dynamic where clause for select options also.

for example

CONCATENATE 'KAPPL' '=' 'C_KAPPL' 'AND'

'KSCHL' 'IN' 'R_KSCHL' 'AND'

'VKORG IN P_VKORG' 'AND'

'VTWEG IN P_VTWEG' 'AND'

'SPART IN P_SPART' 'AND'

'KUNNR IN P_KUNNR' 'AND'

'MATNR IN P_MATNR' 'AND'

'DATBI >=' 'SY-DATUM' 'AND'

'DATAB <=' 'SY-DATUM' INTO GS_CONDITION-WHERE SEPARATED BY SPACE.

The above where clause worked fine in my program.

Regards,

Vara

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:24:23 GMT

Hi Max,

in one single select statement sometimes i want to use VBELN IN S_VBELN sometimes VKORG IN S_VKORG.It will dynamically.We can't say,which one is comming durinf run time.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:28:52 GMT

Hey Vara,

can you tell how you decleared GS_CONDITION-WHERE variable?

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:31:42 GMT

GS_CONDITIOn-WHERE is a character string of length 600.

It is just a string. Depending on your requirement you can set the length of the string.

Regards,

Vara

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:32:33 GMT

Hi Vara

I don't know which release you're using but here It occurs a dump if it doesn't use constant values.

Suresh

If you write this select:

SELECT * FROM VBAK WHERE VBELN IN S_VBELN

AND VKORG IN S_VKORG.

It should be ok for all your cases.

Max

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:33:29 GMT

Hey Vara,

Can u please tell me P_KUNNR is the parameter or select-options.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:33:44 GMT

Hi,

You can use the FM ASEL_CEDST_2_RANGE_WHERE to build the where clause for select-options...

Thanks,

Naren

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:35:21 GMT

Hi max,

For example i'm saying vbeln and vkorg.

In run time it will come other values also.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:35:56 GMT

they are all select-options. I am not using any parameters. Fields with R_ are ranges.

I am using ECC 5.

Regards,

Vara

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:38:03 GMT

Hey Naren,

Can u please show your code what r the values we need to pass and it type also.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:44:04 GMT

Hey Naren,

Can u please show your code what r the values we need to pass and it type also.

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:48:45 GMT

Hi,

Check this example..

TYPE-POOLS: rsds.

TABLES: vbap.

SELECT-OPTIONS so_matnr FOR vbap-matnr.

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

DATA: t_where TYPE rsds_twhere WITH HEADER LINE,

t_cedst TYPE STANDARD TABLE OF cedst,

s_cedst LIKE LINE OF t_cedst.

s_cedst-fnam = 'VBAP~MATNR'.

LOOP AT so_matnr.

MOVE-CORRESPONDING so_matnr TO s_cedst.

APPEND s_cedst TO t_cedst.

ENDLOOP.

CALL FUNCTION 'ASEL_CEDST_2_RANGE_WHERE'

EXPORTING

id_tabname = 'VBAP'

id_xwhere = 'X'

TABLES

it_cedst = t_cedst

et_where = t_where

EXCEPTIONS

selection_not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

READ TABLE t_where INDEX 1.

itab_where[] = t_where-where_tab[].

SELECT SINGLE * FROM vbap

WHERE (itab_where).

IF sy-subrc = 0.

ENDIF.

Please let me know if it works..

Thanks,

Naren

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:50:11 GMT

It's good to know it works in release greater than 4.6.

Anyway

If you're using a release of Vara it seems it works, if you're using a release like mine it doesn't work.

You should write something like this:

SELECT VBELN FROM VBAK INTO TABLE T_ORDER WHERE VBELN IN SO_VBELN.

LOOP AT T_ORDER.

CONCATENATE '''' T_ORDER-VBELN '''' INTO VALUE.

IF SY-TABIX = 1.

CONCATENATE 'VBELN = ' VALUE INTO GI_WHERE SEPARATED BY SPACE.

ELSE.

CONCATENATE 'OR VBELN = ' VALUE INTO GI_WHERE SEPARATED BY SPACE.

ENDIF.

APPEND GI_WHERE.

ENDLOOP.

SELECT *

INTO TABLE GI_VBAK

FROM VBAK

WHERE (GI_WHERE).

But it doesn't make sense because you read VBAK twice.

I believe if you have a select-options like that:

TABLES VBAK.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN,

S_VKORG FOR VBAK-VKORG,

S_KUNAG FOR VBAK-KUNAG.

You can use:

SELECT * FROM VBAK WHERE VBELN IN S_VBELN

AND VKORG IN S_VKORG

AND KUNAG IN S_KUNAG.

This select is better than to use an dynamic clause and it should be always fine.

Max

Re: Dynamic Where clause

Former Member — Tue, 24 Oct 2006 19:06:13 GMT

Hey Naren,

This FM Reterns the values like this

ITAB_WHERE =

( MATNR EQ '000000000000001951' OR

MATNR EQ '000000000000003190' )

Insted of OR i need AND.

Is ther any way.