Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

anmol268
Discoverer
0 Kudos

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

capture1.png

database table: t2

capture2.png

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

Internal table : Itab

capture3.png

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.

5 REPLIES 5

Sandra_Rossi
Active Contributor
0 Kudos

OP question with the screenshots:

database Table1: t1

Sandra_Rossi
Active Contributor

database table: t2

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

Internal table : Itab

michael_piesche
Active Contributor

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?

anmol268
Discoverer
0 Kudos

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;

michael_piesche
Active Contributor

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