SQL: Search a list of columns with a given value (within a row)

Question

I have a table with many columns. Is there a way to make a query that answers the question: "For a particular _id (the primary key), what field(s) in this row have a value of 10"?

EDIT:

Clarification: The table is set up properly. The querying I am doing is some manual querying as I track down some incorrect data. The table has been optimized to be fastest for the automated queries that represent the vast majorities of queries run. (And with more than 95 million rows, every bit of optimization there is important)

I realize that my question is asking to do something SQL wasn't intended to do. I'm just hoping that there is some trick to get what I want.

EDIT for posterity:

In our system, we have many different user accounts. One account is the one we use for all read-only queries (this is the one I use most of the time). It does not own the tables in question, so when I was adapting the answer to my situation, I had to make the following change:

USER_TAB_COLUMNS had to become ALL_TAB_COLUMNS and I had to add OWNER = '[OWNER]' to the query.

Answer 1

This is not a normal piece of database functionality. However you are not the first person who has asked for this, or something like it.

The solution requires two things. The first is the data dictionary; the Oracle database doesn't support Reflection but it does come with a set of views which give us metadata about our database objects. In this case we need user_tab_columns, which will give us the columns for a given table. The second thing is dynamic SQL; this is the ability to assemble a SQL query at runtime and then execute it. There are a couple of ways of do ing this, but usually ref cursors are sufficient.

The following code is a proof of concept. It takes four parameters:

1. the name of table you want to search
2. the name of that table's primary key column
3. the primary key value you want to restrict by
4. the value you want to search for.

It is rough'n'ready so you may need to edit it to tidy the output or to make the program more flexible.

create or replace procedure search_cols
  (tname in user_tables.table_name%type
   , pk_col in user_tab_columns.column_name%type
   , pk in number
   , val in number )
is
    firstcol boolean := true;
    stmt varchar2(32767);
    result varchar2(32767);
    rc sys_refcursor;
begin
    stmt := 'select ';
    << projection >>
    for lrec in ( select column_name from user_tab_columns
                  where table_name = tname
                  and column_name != pk_col
                  and data_type = 'NUMBER'
                  order by column_id )
    loop
        if not firstcol then
            stmt := stmt || chr(10) || '||'',''||';
        else
            firstcol := false;
        end if;
        stmt := stmt || ' case when '|| lrec.column_name||' = '|| val ||
                           ' then '''|| lrec.column_name || ''' else null end';
    end loop projection;
    stmt := stmt || chr(10)|| ' from '||tname||' where '|| pk_col || ' = '|| pk;
    --  dbms_output.put_line(stmt);
    open rc for stmt;
    fetch rc into result;
    close rc;
    dbms_output.put_line(tname || '::' || val || ' found in '||result);
end search_cols;
/

As you can see, dynamic SQL is hard to read. It is harder to debug :) So it is a good idea to have a means to show the final statement.

Anyway, here are the results:

SQL> set serveroutput on size unlimited
SQL> exec search_cols('T23', 'ID', 111, 10)
T23::10 found in ,COL_B,COL_C,

PL/SQL procedure successfully completed.

SQL> exec search_cols('T23', 'ID', 222, 10)
T23::10 found in COL_A,,,

PL/SQL procedure successfully completed.

SQL>

Answer 2

It sounds like your database is not properly normalized. That said, you can probably use the UNPIVOT command inside a subquery to do what you're trying to do.

Answer 3

My solution would use dictionary tables (USER_TAB_COLUMNS) to fetch dynamically the name of all NUMBER columns from your table, and Dynamic SQL, because here I don't see how one could avoid it.

DECLARE
   CURSOR cur_columns IS
          select COLUMN_NAME from USER_TAB_COLUMNS 
          where TABLE_NAME='<MY_TABLE>' and DATA_TYPE='NUMBER';
   query_text VARCHAR2(1000);
   result_value NUMBER;
BEGIN
   -- Iterate through each NUMBER column of the table
   FOR rec_col IN cur_columns LOOP

       -- In my line of primary key <MY_ID>, check if the current column has
       --  the wanted value.
       query_text := 
         'SELECT count(1) FROM <MY_TABLE> WHERE <TABLE_ID> = <MY_ID> AND ' 
         || rec_col.COLUMN_NAME || ' = <MY_VALUE>'; -- < the "magic" is here

       EXECUTE IMMEDIATE query_text INTO result_value; 

       IF result_value > 0 THEN
          DBMS_OUTPUT.PUT_LINE('Got a match for column ' || 
                               rec_col.COLUMN_NAME || '.');
       END IF;

   END LOOP;
END;

Obviously, you'll need to replace all < variables > with their chosen value.

For manual querying, it works fine. However, the performance of this is probably bad, so don't run it against large sets of data as is.