Proper type for OpenSQL IN operand

Question

I have function module which imports my_values

my_values is an custom internal table type of string.

This "my_values" variable contains for example: ["foo", "bar"]

I want to select all values from table Z_MYTAB where the column my_col is in my_values.

I tried this:

SELECT * FROM Z_MYTAB WHERE 
       my_col in @my_values INTO TABLE @DATA(my_rows).

But this fails with an error message:

table my_values has wrong row structure

(The message was translated to English. The original could be slightly different)

I could loop over my_values but I would like to avoid this.

How to do SQL IN with host variables which are internal tables?

I am afraid this is not possible, after IN a range is expected (which is technically also an internal table, just the structure is given (sign, option, low, high)). You have two options: change my_values to a range or instead of IN, use FOR ALL ENTRIES with table my_values — József Szikszai, Feb 27 '19 at 14:55
@JozsefSzikszai thank you for this hint. I used FOR ALL ENTRIES now, and wrote an answer (see below). — guettli, Mar 04 '19 at 10:14

score 3 · Answer 1 · answered Feb 27 '19 at 14:57

Selection with IN is possible only with a range table.

Conversion of an internal table into a range table can be done like this:

DATA ltr_value TYPE RANGE OF string.

ltr_value  = VALUE #( FOR <my_value> IN my_values
                      ( sign   = 'I'
                        option = 'EQ'
                        low    = <my_value> )
                    ).

score 3 · Answer 2 · answered Feb 27 '19 at 15:04

3

IN openands could be of 2 types:

SELECT ... WHERE my_col IN ( value1, value2 , value3)

in this case no host expression can be used as right operand

SELECT ... WHERE my_col IN sel_tab[]

in this case sel_tab is a range like

So you could use the following:

DATA sel_tab type range of string.

sel_tab = value #( for ls in my_values ( sign = 'I' option = 'EQ' low = ls ) ).
SELECT * FROM Z_MYTAB WHERE
       my_col in @sel_tab[] INTO TABLE @DATA(my_rows).

Best regards

answered Feb 27 '19 at 15:04

manuel_b

1,646
3
20
29

Pay attention that in the first variant, there should be **no space before `value1`** (`IN (value1, ...)`) – Sandra Rossi Feb 27 '19 at 15:28
I tried in 7.52 using a [host expression](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abenopen_sql_host_expressions.htm), but it's still not a valid syntax (`my_col IN @( VALUE ty_range_my_col( sign = 'I' option = 'EQ' ( low = 'value1' ) ( low = 'value2' ) ) )` (still not available in 7.53 according to the documentation) – Sandra Rossi Feb 27 '19 at 15:50
1

Host expressions are not possible in the first variant but host variables are possible: `WHERE my_col IN (@value1,@value2)` (and also non-host classic literals `IN ('foo','bar')`) – Sandra Rossi Feb 27 '19 at 19:56

score 2 · Answer 3 · answered Mar 04 '19 at 10:13

User JozsefSzikszai pointed me to "SELECT FOR ALL ENTRIES".

I found this in the docs:

For an elementary row type, the pseudo component table_line must be specified for comp.

See: https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenwhere_logexp_itab.htm

IF my_values is initial.
  exit.
endif.

SELECT * FROM Z_MYTAB
  FOR ALL ENTRIES IN @my_values WHERE
  column_name = @my_values-table_line
  INTO TABLE @DATA(result_rows).

Proper type for OpenSQL IN operand

3 Answers3