ORACLE: SQL syntax to find table with two columns with names like ID, NUM-CodePudding

My question is based on: Finding table with two column names If interested, please read the above as it covers much ground that I will not repeat here.

For the answer given, I commented as follows:

NOTE THAT You could replace the IN with = and an OR clause, but generalizing this to like may not work because the like could get more than 1 count per term: e.g.

SELECT OWNER, TABLE_NAME, count(DISTINCT COLUMN_NAME) as ourCount 
FROM all_tab_cols WHERE ( (column_name LIKE '%ID%') OR (COLUMN_NAME LIKE '%NUM%') ) 
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(DISTINCT column_name) >= 2 
ORDER BY OWNER, TABLE_NAME ;

This code compiles and runs. However, it will not guarantee that the table has both a column with a name containing ID and a column with a name containging NUM, because there may be two or more columns with names like ID.

Is there a way to generalize the answer given in the above link for a like command. GOAL: Find tables that contain two column names, one like ID (or some string) and one like NUM (or some other string).

CodePudding user response：

You may use conditional aggregation as the following:

SELECT OWNER, TABLE_NAME, COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END) as ID_COUNT,
  COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END) NUM_COUNT
FROM all_tab_cols
GROUP BY OWNER, TABLE_NAME 
HAVING COUNT(CASE WHEN COLUMN_NAME LIKE '%ID%' THEN COLUMN_NAME END)>=1 AND
       COUNT(CASE WHEN COLUMN_NAME LIKE '%NUM%' THEN COLUMN_NAME END)>=1
ORDER BY OWNER, TABLE_NAME ;

See a demo.

If you want to select tables that contain two column names, one like ID and one like NUM, you may replace >=1 with =1 in the having clause.

CodePudding user response：

If I understood you correctly, you want to return tables that contain two (or more) columns whose names contain both ID and NUM (sub)strings.

My all_tab_cols CTE mimics that data dictionary view, just to illustrate the problem.

EMP table contains 3 columns that have the ID (sub)string, but it should count as 1 (not 3); also, as that table doesn't contain any columns that have the NUM (sub)string in their name, the EMP table shouldn't be part of the result set
DEP table contains one ID and one NUM column, so it should be returned

Therefore: the TEMP CTE counts number of ID and NUM columns (duplicates are ignored). The final query expects that table contains both columns.

Sample data:

SQL> with all_tab_cols (table_name, column_name) as
  2    (select 'EMP', 'ID_EMP' from dual union all
  3     select 'EMP', 'ID_MGR' from dual union all
  4     select 'EMP', 'SAL'    from dual union all
  5     select 'EMP', 'DID_ID'  from dual union all
  6     --
  7     select 'DEP', 'ID_DEP' from dual union all
  8     select 'DEP', 'DNUM'   from dual union all
  9     select 'DEP', 'LOC'    from dual
 10    ),

Query begins here:

 11  temp as
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) = sum(cnt_num)
 26     and sum(cnt_id) = 1;

TABLE_NAME
--------------------
DEP

SQL>

CodePudding user response：

You could do a UNION ALL and then a GroupBy with a Count on a subquery to determine the tables you want by separating your query into seperate result sets, 1 based on ID and the other based on NUM:

SELECT *
FROM
(
    SELECT OWNER, TABLE_NAME
    FROM all_tab_cols 
    WHERE column_name LIKE '%ID%'
    GROUP BY OWNER, TABLE_NAME
    UNION ALL
    SELECT OWNER, TABLE_NAME 
    FROM all_tab_cols 
    WHERE column_name LIKE '%NUM%'
    GROUP BY OWNER, TABLE_NAME
) x
GROUP BY x.OWNER, x.TABLE_NAME 
HAVING COUNT(x.TABLE_NAME) >= 2 
ORDER BY x.OWNER, x.TABLE_NAME ;

CodePudding user response：

This is essentially an "edit" of Littlefoot's answer, that I believe makes things better. I give due credit, but I was asked to make this a separate answer, so I am doing so.

 11  temp as -- USE WITH IF not using the data part above
 12    (select table_name, column_name,
 13       sum(case when regexp_count(column_name, 'ID') = 0 then 0
 14                when regexp_count(column_name, 'ID') >= 1 then 1
 15           end) cnt_id,
 16       sum(case when regexp_count(column_name, 'NUM') = 0 then 0
 17                when regexp_count(column_name, 'NUM') >= 1 then 1
 18           end) cnt_num
 19     from all_tab_cols
 20     group by table_name, column_name
 21    )
 22  select table_name
 23  from temp
 24  group by table_name
 25  having sum(cnt_id) >= 1
 26     and sum(cnt_num) >= 1;