Dynamic Where clause

[Former Member]

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 (<b>GI_WHERE</b>).

In that where clause contain ranges.

for eg.

GI_WHERE = <b>'VBELN IN S_VBELN'</b>.

Thanks,

Suresh.

Message was edited by: Suresh Kumar

[Former Member]

Hi

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

[Former Member]

Hey max bianchi,

Then what is the solution for that?

[Former Member]

Hi

U should convert the SELECT-OPTIONS.

But I should know what you need to do exactly

Max

[Former Member]

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.

[Former Member]

Hi

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

Why don't you write simple

SELECT * FROM VBELN IN S_VBELN ?

Max

[Former Member]

Hi Max,

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

[Former Member]

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

[Former Member]

Hi max,

For example i'm saying vbeln and vkorg.

In run time it will come other values also.

[Former Member]

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

[Former Member]

Hey Vara,

can you tell how you decleared <b>GS_CONDITION-WHERE</b> variable?

[Former Member]

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

[Former Member]

Hey Vara,

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

[Former Member]

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

I am using ECC 5.

Regards,

Vara

[Former Member]

Hi

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

[Former Member]

Hi,

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

Thanks,

Naren

[Former Member]

Hey Naren,

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

[Former Member]

Hey Naren,

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

[Former Member]

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

[Former Member]

Hey Naren,

This FM Reterns the values like this

ITAB_WHERE =

( MATNR EQ '000000000000001951' <b>OR</b>

MATNR EQ '000000000000003190' )

Insted of <b>OR</b> i need <b>AND</b>.

Is ther any way.

[Former Member]

Hey Naren,

This FM Reterns the values like this

ITAB_WHERE =

( MATNR EQ '000000000000001951' <b>OR</b>

MATNR EQ '000000000000003190' )

Insted of <b>OR</b> i need <b>AND</b>.

Is ther any way.

[RichHeilman]

Suresh, <b>AND</b> in this case does not make sense, because MATNR can never be 1951 <b>AND</b> 3190 at the same time, you must use <b>OR</b>.

Regards,

Rich Heilman

[RichHeilman]

Also, BTW, the use of the <b>IN</b> operator in a dynamic where clause is prohibited in release 46c. You may be able to use it in newer releases, not sure.

Regards,

Rich Heilman

[Former Member]

Hi Rich Heilman,

Right now i'm using 4.6C only.

Message was edited by: Suresh Kumar

[RichHeilman]

Well that is the reason why you are having so much trouble using your select-options in your dynamic where clause, the IN operator is not allowed.

Regards,

Rich Heilman

[Former Member]

Hey Rich Heilman,

So what is the solution?I'm doeing R&D in last 14 hrs.

Thanks,

Suresh.

[Former Member]

Hi,

As Rich mentioned..You cannot have <b>AND</b> for MATNR..As single row cannot have two values for the same field..

Thanks,

Naren