Skip to Content

Finding occurrence of a word in a database table using another Table

I want a open sql select query which can count the number of occurances of each word in the database table using another table

following is the scenario:

database Table1: t1


database table: t2


After query execution result should go in a internal table like shown

Internal table : Itab


Note : - The words column of T1 is of String format, so we cannot use it directly in where, order by, group by clause. It shows an error. So, Please provide a query keeping this in mind.

capture1.png (5.7 kB)
capture2.png (2.9 kB)
capture3.png (4.2 kB)
Add a comment
10|10000 characters needed characters exceeded

  • You say you want a query. What kind of query exactly?

    • OPEN SQL
    • native SQL (e.g. Oracle/Hana)
    • CDS view?

    The task seems very simple to do in ABAP (loop, split, match, count), the biggest 'problem' you have to address is performance, especially if t1 are lines out of a book and t2 are words out of a dictionary.

    The next problem will be identifying words in t1 based on knowing the relevant separators which based on your provided example would be only comma and space. Are those all relevant separators? What about colon and semi-colon? Should the separators be all non-alphabet characters (e.g. numbers, hypen, etc.)?

    What have you achieved so far? Where do you stand with your solution?

  • Hello Michael Piesche

    I want an Open SQL query.

    I did it using loops, which is as You said very Performance heavy process.

    Words in T1 are saperated by comas and space, this format is constant for the whole table.

    I found out a query in SQL, not able to convert in Open SQL for abap.

    SELECT T2.*, (SELECT COUNT(*) FROM T1 WHERE T1.words LIKE CONCAT('%',T2.words,'%')) AS COUNT FROM T2;

  • Anmol Rawat, what ABAP version are you on? 7.40, 7.51, 7.54? The higher the version, the more options you have with Open SQL, there have been quite some improvements. But I currently doubt that your entire requirement can be done today with Open SQL. The native SQL statement that you have can definitly not be translated into Open SQL without changing the logic.

    My assumption is, that you currently might have the most success with a CDS view, if you want to solve the issue by the SQL server on a database level.

    The other option would be to solve it with ABAP after loading the contents of t1 and t2 tables. The overall performance on the SAP application server, if done right, shouldnt be that much worse then if the SQL Server does it for you. May be some hints can be given how to optimize your current ABAP approach.

    Did you actually try out your native SQL query on the SQL server, either through ST04 (e.g. Oracle SQL command editor) or on a frontend for the SQL server directly? How was that performance? I assume that performance couldnt be that great either with the current query, since the LIKE comparison is going to run through each complete line of t1 for each t2 value. If you want to, I could post a pseudo-code in ABAP for how I would approach this problem.

    " 1) Conactenate more than 2 literals
    " CONCAT('%',T2.words,'%'))   => CONCAT( CONCAT( '%', T2~words ), '%' )
    " 2) Concatenate in WHERE LIKE restriction
    " WHERE T1.words LIKE CONCAT( ... )            => not supported by 7.54
    " 3) Inner select statement to create column values for outer select statement
    " SELECT T2.*, ( SELECT ... ) AS COUNT FROM T2 => not supported by 7.54

Assigned Tags

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.