SELECT by column of type STRING

4
votes

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?

3
abapopensql
What if you remove into table lt_journal from the query? – GMB
``` Select single * from DB_Journal into ls_journal where journal like '%football'. ``` also doesnt work – HUJ
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
Actually the selection is more complex and 3 joined tables, i tried to make the example as simple as possible – HUJ
@HUJ GMB certainly means Select * from DB_JOURNAL where journal like '%Football' – C.Champagne

3 Answers

4
votes

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.

1
votes

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.
0
votes

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