Select statement with offset?

Question

I'm trying to use this SELECT statement in ABAP:

  SELECT DISTINCT * FROM  dbtab
     INTO CORRESPONDING FIELDS OF TABLE itab
     WHERE  field1+7(16)  IN s_field1
     AND    field2        IN s_field2.

but I can't use offset for a dbtab column. How can I solve this problem?

I'm trying to avoid loop like

  SELECT DISTINCT * FROM  dbtab
     WHERE  field2        IN s_field2.
       IF field1+7(16)  IN s_field1
           ...
       endif.
  endselect.

You are using ranges/select options in your selection, so you could try a contains pattern (CP) entry in the ranges field (Sorry, I have actual no SAP system available to create an example. My answer will follow when I have a system available - unless somebody else answers :) — knut, Jan 15 '16 at 19:43
What do you select with `s_field1+7(16)`? A list of values or is it a complex selection (greater then, patterns...)? The solution in my comment would be (more or less) easy for ranges and single values. patterns etc. may become more difficult. — knut, Jan 19 '16 at 09:52

Nelson Miranda · Accepted Answer · 2016-01-15T17:32:47.757

6

You can't use offset in OPEN SQL.

I would recommend to make a SELECT into an internal table and loop over it like this.

SELECT DISTINCT * FROM dbtab
INTO CORRESPONDING FIELDS OF TABLE itab
WHERE field2 IN s_field2.

LOOP AT dbtab into wa_itab.
  IF wa_itab-field1+7(16) IN s_field1
    ...
  ENDIF.
ENDLOOP.

On the other hand I would also define the internal table as SORTED or HASHED or if you prefer try to SORT itab by the field you are making the comparison. Field symbols could be an alternative also.

Hope it helps.

edited Jan 15 '16 at 17:32

answered Jan 15 '16 at 16:58

Nelson Miranda

5,484
5
33
54

1

For better performance it is recommended to use field-symbols in a loop, rather than work areas. – Suncatcher Jan 16 '16 at 17:20
1

@Suncatcher Agreed. Best regards. – Nelson Miranda Jan 16 '16 at 21:18

score 1 · Answer 2 · answered Jan 16 '16 at 00:41

If your policies allow you to you can use probably use an EXECUTE_SQL block instead, for example if you have an Oracle backend, which allows you to leverage native SQL constructs instead of just OPEN SQL. It's a judgement call whether this is justified or not depending on the performance gain. I think it's probably not.

You could also use a LIKE clause, which isn't as efficient as exactly knowing the offset, but would allow you to encapsulate each option with %: WHERE field 1 LIKE '%search_partial%' OR field1 LIKE ...

What is the use-case for this, it might be there's a more appropriate option if we have some context.

Select statement with offset?

2 Answers2