WHERE condition IN operator problem

[former_member719641]

Hi,

I am using SQL statetment like this:

select * from zvst_pass INTO TABLE it_vpass WHERE PLANT IN (WA_USER-AUTH_PLANT).

Here WA_USER-AUTH_PLANT has value '1300,1200'. The above statement gives the result only for plant '1300'.

When we use above statement like this:

select * from zvst_pass INTO TABLE it_vpass WHERE PLANT IN ('1300','1200').

It gives us the perfect result.

[FabioPagoti]

If what you want to do is a dynamic SQL where clause, then wrap the whole WHERE condition in a string, not just one side of the comparison.

Example:

data v_where type string.

v_where = |PLANT IN ${WA_USER-AUTH_PLANT}|.

select * from zvst_pass INTO TABLE it_vpass WHERE (v_where).

If possible, consider using ranges instead of doing a dynamic SQL in this simple query.

[Sandra_Rossi]

dollar?

should be

SPLIT WA_USER-AUTH_PLANT AT ',' INTO TABLE DATA(auth_plants).
v_where = |PLANT IN ('{ concat_lines_of( table = AUTH_PLANTS sep = |','| ) }')|.
select * from zvst_pass INTO TABLE it_vpass WHERE (v_where).

[FredericGirod]

Because a WHERE ... IN ... statement expect a RANGE not a list of values.

data rt_plants type range of werks_d.
split wa_user-auth_plant at ',' into data(plant_list_text).
loop at plant_list_text reference into data(o_ligne_plant).
  append #( sign = 'I'  option = 'EQ'  low = o_ligne_plant->* ) to rt_plants.
endloop. 

select * from zvst_pass INTO TABLE it_vpass WHERE PLANT IN rt_plants.

[FabioPagoti]

There are at least 3 possible uses of the IN operator.

• Ranges
• List of values
• Subqueries

One of them does accept a list of values.

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/index.htm

[FredericGirod]

319481ee5c07417784f1f1ae49b6371f you're right !

I never see it, never use it, thanks for the info

The example in ABAPDOCU uses it without dynamic SQL

SELECT * 
       FROM sbook 
       WHERE class NOT IN ('C','F','Y') 
       INTO TABLE @DATA(sbook_tab).