Trouble Writing SQL Query....

Question

I'm having trouble writing a query. I have a support tabled called 'MYTABLE' and it has a column called 'TABLENAME' that can have one or many table names in it. Multiple Tables are separated with commas.

Example:

TBLUSER
TBLUSER, TBLACCOUNT

I'm trying to write an query that will identify any entries in the MYTABLE table that are not valid tables in the database. I was able to write the follow....

                     SELECT *
                       FROM MYTABLE T1
            LEFT outer JOIN ALL_TAB_COLS T2
                         ON (    upper(T1.TABLENAME) = upper(t2.Table_Name) 
                             AND T2.Owner = 'ME'
                             )
                       WHERE TABLE_NAME IS NULL;

And it works exactly how I want - but it only works when the entry in MYTABLE contains a single table. When there are multiple tables separated by commas - it fails. My SQL skills are somewhat lacking and my natural instinct is to 'Do a For Each' but I feel that's not the right approach (and I have no idea how to do that in SQL).

Answer 1

You seriously need to rethink your database design there. Keeping multiple entries on a single record in a table that is supposed to keep track of those entries is a big giant WTF.

You need to change your design so that instead of what you describe you have something like:

ID    TABLENAME
----------------------
1     TBLUSER
2     TBLUSER
2     TBLACCOUNT

Where the ID + Tablename is a composite primary key. This would make your query you have wrote work (although not work based on the bare-bones example I provided above).

NOTE I know that this may not be what you are looking for in your exact problem, but I feel it is important to say anyway because future users may come and find this problem and need a better understanding of database normalization practices (which you may not be able to do since the application is as it is because "that's just how it is").

Answer 2

You're storing a string in MYTABLE.TABLENAME and trying to match it against a string in ALL_TAB_COLS.TABLE_NAME (which btw, I don't see any reason you would use ALL_TAB_COLS instead of ALL_TABLES in this case).

If your string is 'TBLUSER, TBLACCOUNT', it's not going to be equal to the string 'TBLUSER' or the string 'TBLACCOUNT'. That's all the expression upper(T1.TABLENAME) = upper(t2.Table_Name) is testing -- are these two string equal? You seem to be expecting that it somehow "knows" that your data happens to be a comma-separated list of table names.

The brute-force method to make what you have work using string comparisons is to change the condition to ','||upper(T1.TABLENAME)||',' LIKE '%,'||upper(t2.Table_Name)||',%. So you basically would be looking at whether TABLE_NAME is a substring of your TABLENAME column value.

However, the real point is that this is a not very good database design. First of all, from a simple point of clarity, why would you name a column "TABLENAME" (singular) and then put values in it that represent multiple table names? If you're going to do that, you should at least call it something like "TABLENAMELIST".

More importantly, this is not generally the way we do things in relational databases. If your MYTABLE looks like this:

ID       TABLENAME
1        TBLUSER
2        TBLUSER, TBLACCOUNT

then a more proper relational way to design the table would be:

ID       TBL_NUM    TABLENAME
1        1          TBLUSER
2        1          TBLUSER
2        2          TBLACCOUNT

Then your query would work as-is, more or less, because the TABLENAME column would always contain the name of a single table.

Answer 3

The short answer is:

select distinct
  atc.table_name
from
  mytable mt
 ,all_tab_cols atc
where atc.owner = 'SOMESCHEMA'
  and (
       mt.tablename = atc.table_name
       or
       (
        0 < instr(','||replace(upper(mt.tablename),' ','')||','
                          ,','||upper(atc.table_name)||',')
       )
      )

The long answer was already well described by David Costa's post.