topic WHERE condition IN operator problem in Application Development and Automation Discussions

WHERE condition IN operator problem

former_member719641 — Mon, 29 Nov 2021 12:41:40 GMT

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.

Re: WHERE condition IN operator problem

FabioPagoti — Mon, 29 Nov 2021 12:49:10 GMT

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.

Re: WHERE condition IN operator problem

FredericGirod — Mon, 29 Nov 2021 12:52:00 GMT

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.

Re: WHERE condition IN operator problem

FabioPagoti — Mon, 29 Nov 2021 13:16:25 GMT

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

Re: WHERE condition IN operator problem

FredericGirod — Mon, 29 Nov 2021 13:33:46 GMT

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).

Re: WHERE condition IN operator problem

Sandra_Rossi — Mon, 29 Nov 2021 14:53:37 GMT

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).