SELECT by column of type STRING

Question

I'll try to explain as simple as possible.

I have a Database table 'DB_JOURNAL' that has only 2 columns 'Date' and 'Journal' type String.

example :

01.01.2020 | I played football.

02.02.2020 | I played basketball

I want to write a Select statement and in the query to search for the string. Like searching for the word football.

Select * from DB_JOURNAL into table lt_journal
       where journal like '%football%'.

This is not allowed in ABAP:

The field "JOURNAL" is a long string or a text and cannot be used in the WHERE condition.

Is there any solution?

``` Select single * from DB_Journal into ls_journal where journal like '%football'. ``` also doesnt work — HUJ, Jan 24 '20 at 13:59
I don't know ABAP but `Select * from table1 into table2` copies the content of table1 to table2. Is that what you REALLY want? — C.Champagne, Jan 24 '20 at 14:00
Actually the selection is more complex and 3 joined tables, i tried to make the example as simple as possible — HUJ, Jan 24 '20 at 14:01
@HUJ GMB certainly means `Select * from DB_JOURNAL where journal like '%Football'` — C.Champagne, Jan 24 '20 at 14:01
@SandraRossi, thanks for your input, the example was only to simplify the problem. My goal is searching a database for a field that has String type, that abap doesn't accept. — HUJ, Jan 24 '20 at 14:15
Please add this text to your question, it will help avoid misunderstandings: "When activating this code, I get the error message 'The field "JOURNAL" is a long string or a text and cannot be used in the WHERE condition.' by the compiler. Is there any way to do such a selection without changing the STRING datatype of the database column?" — rplantiko, Jan 24 '20 at 15:32

score 4 · Accepted Answer · edited Jan 29 '20 at 14:00

4

The only solution is to use the native SQL, i.e. the SQL of your database.

Native SQL can be run in several flavors.

The shortest code but also the oldest one (SAP recommends not using it) is with the statement EXEC SQL:

DATA text TYPE string.
EXEC SQL PERFORMING sr.
  SELECT text FROM sotr_textu INTO :text WHERE text LIKE '%e%'
ENDEXEC.
FORM sr.
  WRITE / text.
ENDFORM.

NB: the code above works fine with HANA, MSSQL and Oracle databases, but you need to set table and column names in upper case with MaxDB.

Example of more complex query:

DATA: text   TYPE string,
      status TYPE string.

status = 'R'.

TRY.
    EXEC SQL PERFORMING sr.
      SELECT TEXT FROM SOTR_TEXTU INTO :text
           WHERE  STATUS  = :status
             AND  TEXT    LIKE '%e%'

    ENDEXEC.
  CATCH  cx_sy_native_sql_error INTO DATA(exc).
    cl_demo_output=>display( exc->get_text( ) ).
ENDTRY.
FORM sr.
  WRITE / text.
ENDFORM.

for case-insensitive search:

 AND  UPPER(TEXT) LIKE UPPER(:text)

Other ways are ADBC and AMDP.

ADBC has superseded EXEC SQL.

edited Jan 29 '20 at 14:00

HUJ

47
1
6

answered Jan 24 '20 at 16:57

Sandra Rossi

11,934
5
22
48

Life saver. Thank you so much! I don't know why the compiler didn't accept as I tried it before. But with this syntax it works! – HUJ Jan 27 '20 at 07:47
4

SQL inside a `EXEC SQL`/`ENDEXEC` block ("native SQL") is syntactically different from SQL without such a block (ABAP SQL). "Native SQL" is directly interpreted and run by the database, while ABAP SQL is first interpreted by ABAP, which allows only a very limited syntax, and transformed at runtime into SQL which can be understood by the database. **NB: inside a `EXEC SQL`/`ENDEXEC` block, don't enter the classic final dot.** – Sandra Rossi Jan 27 '20 at 08:04
Note: Set the table name and the columns in Capital Letters – HUJ Jan 28 '20 at 13:24
1

@HUJ In lower case, it works well with HANA database. I have added a mention of your own experience. – Sandra Rossi Jan 28 '20 at 13:36
I will edit it 1 more time for litte more complex query – HUJ Jan 28 '20 at 14:19
@HUJ Can you edit to mention what database you have please? – Sandra Rossi Jan 28 '20 at 14:33
1

I tried it on 3 different Systems: it worked on MSSQL and Oracle, but not on MaxDB – HUJ Jan 29 '20 at 08:57

score 1 · Answer 2 · answered Jan 25 '20 at 15:35

1

The only way to avoid Native SQL which proposed by Sandra is to use looped SELECT:

DATA: lt_journal TYPE TABLE OF db_journal WITH EMPTY KEY.

SELECT *
 FROM DB_JOURNAL
 INTO @DATA(wa).
 CHECK wa-journal CP '*football*'.
 APPEND wa TO lt_journal.
ENDSELECT.

answered Jan 25 '20 at 15:35

Suncatcher

10,355
10
52
90

1

Thanks for your answer, I believe this works with tiny tables, but tables with 1M record this will take a while... – HUJ Jan 27 '20 at 07:45
1

Yep, it depends on your use-case (you simplified your real scenario). If it is selection by only single string field it will take a lot of time, Native SQL/AMDP/ADBC/CDS will be definitely better – Suncatcher Jan 27 '20 at 11:08

score 0 · Answer 3 · edited Jan 25 '20 at 16:25

0

Are you sure that column "Journal" is of type String? Can you change it to some existing data element CHAR40 or something...

Also consider using

Select * from DB_JOURNAL into CORRESPONDING FIELDS OF table lt_journal where journal like '%football'.

edited Jan 25 '20 at 16:25

Sandra Rossi

11,934
5
22
48

answered Jan 24 '20 at 14:52

djomla87

93
1
3
13

Yes, the challenge is that the column "Journal" is of type String? – HUJ Jan 24 '20 at 15:01
The program doesnt accept this `Select * from DB_JOURNAL into CORRESPONDING FIELDS OF table lt_journal where journal like '%football'` – HUJ Jan 24 '20 at 15:03

SELECT by column of type STRING

3 Answers3