Oracle搜索字符串中的单词列表并重新定义现有单词 [英] Oracle search list of words in string and retruen existing ones

问题描述

我正在尝试编写有关所使用的数据仓库表的统计信息.

I am trying to write a statistic about the data warehouse tables that are used.

因此，我应该进行查询以携带用过的桌子.

Therefor I should make a query to bring the used tables.

我有一个表名列表，我有一个数据库表，查询存储在其中.

I have a list of table names and I have a database table, where the querys stored in.

如何选择使用表名的行?

How can I select the rows with the table names that used in?

Report Table:
...
QUERYTEXT String
...


select * from Report where QUERETEXT (?)

DWTableNames: DWA, DWB,..

我要使用querytext字段中存在的数据仓库表名称来打印每个报表.

I want to print each Report with the data warehouse table names that are existing in the querytext field.

推荐答案

这是我搜索的解决方案:

Here is the solution that I searched for:

WITH dwtables(dwtable) AS (
  SELECT 'DWA' FROM dual UNION ALL
  SELECT 'DWB' FROM dual UNION ALL
  SELECT 'DWC' FROM dual

)--> Tabelle mit einem Spalte (Liste)

SELECT title, 
    (SELECT LISTAGG (dwtable, ', ' ) WITHIN GROUP (ORDER BY dwtable)
     FROM dwtables
    WHERE REGEXP_LIKE (r.querytext, '(^|\s)'||dwtables.dwtable||'(\s|$)', 'i')) AS dwtables

 FROM Reports r;

这篇关于Oracle搜索字符串中的单词列表并重新定义现有单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！