Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Doubt on dynamic where clause...

Former Member
0 Kudos

Hello ABAPers,

I have a doubt on how to use dynamic where clause.

Please help me out.

Table T_CONFIG has:

FNAME:

STCD1

STCD2

then I want,

Select LFA1~LIFNR into S_LFA1

from LFA1

inner join LFB1

on LFA1LIFNR = LFB1LIFNR

<b>where LFA1~LAND1 = I_LFA1-LAND1 AND

LFB1~BUKRS = I_LFB1-BUKRS

AND ( LFA1~STCD1 = I_LFA1-STCD1 OR

LFA1~STCD2 = I_LFA1-STCD2)</b>

Now when T_config table contains three values in its fields as follows:

If Table T_Config has

FNAME:

STCD1

STCD2

STCD3

then I want:

Select LFA1~LIFNR into S_LFA1

from LFA1

inner join LFB1

on LFA1LIFNR = LFB1LIFNR

<b>where LFA1~LAND1 = I_LFA1-LAND1 AND

LFB1~BUKRS = I_LFB1-BUKRS

AND ( LFA1~STCD1 = I_LFA1-STCD1 OR

LFA1~STCD2 = I_LFA1-STCD2 OR

LFA1~STCD3 = I_LFA1-STCD3)</b>

Hence depending on the number of values in the fields on table T_confgi <b>where clause is dynamic</b>. Can you please tell me how do I do dynamic where clause in above select statement?

1 ACCEPTED SOLUTION

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Not really sure about your logic here, but here is an example of a dynamic where clause.




REPORT ZRICH_0003 .

 data: s_lfa1 type lfa1.

 data: where_clause(100) Type c occurs 0 with header line.

 where_clause = 'lfa1~land1 = i_lfa1-land1'.
 append where_clause.

 where_clause = 'and lfb1~bukrs = i_lfb1-bukrs'.
 append where_clause.

 where_clause = 'and (  LFA1~STCD1 =  I_LFA1-STCD1'.
 append where_clause.

 where_clause = 'or LFA1~STCD2 =  I_LFA1-STCD2'.
 append where_clause.


 where_clause = 'or LFA1~STCD3 =  I_LFA1-STCD3 ).'.
 append where_clause.

 Select Single LFA1~LIFNR into S_LFA1
            from LFA1
                inner join LFB1
                     on LFA1~LIFNR = LFB1~LIFNR
                        where (where_clause).
*                       where LFA1~LAND1 = I_LFA1-LAND1 AND
*                             LFB1~BUKRS = I_LFB1-BUKRS
*                         AND ( LFA1~STCD1 = I_LFA1-STCD1 OR
*                               LFA1~STCD2 = I_LFA1-STCD2 OR
*                               LFA1~STCD3 = I_LFA1-STCD3)


check sy-subrc = 0.

Regards,

Rich Heilman

3 REPLIES 3

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Not really sure about your logic here, but here is an example of a dynamic where clause.




REPORT ZRICH_0003 .

 data: s_lfa1 type lfa1.

 data: where_clause(100) Type c occurs 0 with header line.

 where_clause = 'lfa1~land1 = i_lfa1-land1'.
 append where_clause.

 where_clause = 'and lfb1~bukrs = i_lfb1-bukrs'.
 append where_clause.

 where_clause = 'and (  LFA1~STCD1 =  I_LFA1-STCD1'.
 append where_clause.

 where_clause = 'or LFA1~STCD2 =  I_LFA1-STCD2'.
 append where_clause.


 where_clause = 'or LFA1~STCD3 =  I_LFA1-STCD3 ).'.
 append where_clause.

 Select Single LFA1~LIFNR into S_LFA1
            from LFA1
                inner join LFB1
                     on LFA1~LIFNR = LFB1~LIFNR
                        where (where_clause).
*                       where LFA1~LAND1 = I_LFA1-LAND1 AND
*                             LFB1~BUKRS = I_LFB1-BUKRS
*                         AND ( LFA1~STCD1 = I_LFA1-STCD1 OR
*                               LFA1~STCD2 = I_LFA1-STCD2 OR
*                               LFA1~STCD3 = I_LFA1-STCD3)


check sy-subrc = 0.

Regards,

Rich Heilman

Former Member
0 Kudos

the question is I don't know how many fields are going to be in T_zconfig. There may be STCD1, STCD2, STCD3, STCD4, STCD5,....

so how do I append to (where_clause) clause then using further loops on T_zocnfig table.

Thanks.

0 Kudos

Hi Tushar,

1. U must be knowing about dynamic sql.

2. in our sql, we can provide WHERE conditions

in a variable .

3. The Technique is to BUILD this where conditions

dynamically in a CHAR(200) eg. variable.

4. see this code (just copy paste)

to get an idea how it works.

REPORT abc.

DATA : b LIKE TABLE OF bseg WITH HEADER LINE.

DATA : mywhere(200) TYPE c.

DATA : itab LIKE TABLE OF t001 WITH HEADER LINE.

mywhere = 'BUKRS = ''1000'' '.

SELECT * FROM t001

INTO TABLE itab

WHERE (mywhere).

BREAK-POINT.

5. NOW FOR YOUR PROBLEM.

1. Use the FM DDIF_TABL_GET

(to get all fields of the required table)

2. Loop at the internal table DD03P_TAB

3. Search for your STCD fields using string

operator/comparison CS,CA etc.

4. Build your where condition

dynamnically

5. After that, pass this string to

the sql - some what like above (this)

SELECT * FROM t001

INTO TABLE itab

WHERE (mywhere).

THE VALUE WHICH U CONSTRUCT/BUILD IN MYWHERE

IS VERY IMPORTANT.

I hope it helps.

regards,

amit m.