topic Dynamic Where clause in Application Development and Automation Discussions

Dynamic Where clause

Former Member — Tue, 24 Oct 2006 18:11:47 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.

Re: Dynamic Where clause

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

Hi,

I believe you cannot use variables in the dynamic where clause..

It can only be literals..

Allowing

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

itab = ' MATNR = ''TEST'' '.

APPEND ITAB.

select single * from mara

where (itab).

Not allowing (giving short dump)

TABLES: MARA.

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

SELECT-OPTIONS: SO_MATNR FOR MARA-MATNR.

itab = ' MATNR IN SO_MATNR '.

APPEND ITAB.

select single * from mara

where (itab).

Thanks,

Naren

Re: Dynamic Where clause

seshatalpasai_madala — Wed, 25 Oct 2006 07:55:14 GMT

Hi,

You can do this by building a String of what you want to put in the where clause.

Ex:

DATA: where_string type string.

concatenate where_string 'VBELN IN S_VBELN' into where_string separated by space.

You can use variable to build this string.

You can also check if this variable is having any value and then build

if var is not initial.

build the where_stirng here.

endif.

Use it like this

SELECT * FROM table_name into table it_tab where ( where_string ).

Best Regards,

Sesh

Re: Dynamic Where clause

athavanraja — Sun, 29 Oct 2006 09:47:28 GMT

you can do this the syntax is

select * from (query_table) into <itab> where (options).

options is of type standard table of RFC_DB_OPT.

in options you would pass values like

CARRID = 'LH'

check this code in FM RFC_READ_TABLE

Regards

Raja

Re: Dynamic Where clause

anversha_s — Sun, 29 Oct 2006 09:49:54 GMT

Hello Suresh

If you are already working an 6.40 (or higher) than you can simply concatenate the WHERE condition into a string and code:

DATA:

gd_where_conditions TYPE string.

SELECT * FROM ...

WHERE ( gd_where_conditions ).

However, if you are working on 6.20 (or less) than you could use the following function module to create your dynamic WHERE condition(s): RH_DYNAMIC_WHERE_BUILD

The function module returns the WHERE conditions in CONDTAB. Thus, you could code:

TYPES: BEGIN OF ty_s_clause.

TYPES: line(72) TYPE c.

TYPES: END OF ty_s_clause.

DATA:

gt_where_clauses TYPE STANDARD TABLE OF ty_s_clause

WITH DEFAULT KEY.

DATA:

gt_condtab TYPE STANDARD TABLE OF hrcond.

Fill gt_condtab with the required conditions

CALL FUNCTION 'RH_DYNAMIC_WHERE_BUILD'

EXPORTING

dbtable = space " can be empty

TABLES

condtab = gt_condtab

where_clause = gt_where_clauses

EXCEPTIONS

empty_condtab = 01

no_db_field = 02

unknown_db = 03

wrong_condition = 04.

Select your data

SELECT * FROM ...

WHERE (gt_where_clauses).

Regards

anver

pls mark all hlpful answers

Re: Dynamic Where clause

Former Member — Mon, 30 Oct 2006 06:30:59 GMT

Hi,

Please find the below code

DATA: gi_where TYPE STANDARD TABLE OF rsdswhere,

wa_where TYPE rsdswhere.

*rsdswhere is the structure to be used for dynamic where

*conditions

wa_where = 'VBELN IN S_VBELN'.

APPEND wa_where TO gi_where.

SELECT *

FROM VBAK

INTO it_vbak

WHERE gi_where.

Regards,

Raghavendra

Re: Dynamic Where clause

Former Member — Mon, 30 Oct 2006 06:36:46 GMT

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

SELECT * FROM VBAK INTO TABLE GI_VBAK

WHERE (CONDITION).

Also have a look at below info.

To specify a condition dynamically, use:

SELECT... WHERE (itab)...

where itab is an internal table with line type c and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written into the lines of itab. However, you may only use literals, and not the names of data objects. The internal table itab can also be left empty.

If you only want to specify a part of the condition dynamically, use:

SELECT... WHERE cond AND (itab)...

You cannot link a static and a dynamic condition using OR.

You may only use dynamic conditions in the WHERE clause of the SELECT statement.

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers