SQL: search a list of columns with a given value (inside a row)

Question

I have a table with many columns. Is there a way to make a request that answers the question: "For a particular _id (primary key), which field on this line has a value of 10"?

EDIT:

Clarification: the table is configured correctly. The queries that I execute are some manual queries when I track down some wrong data. The table is optimized to be the fastest for automated queries that represent the vast majority of queries. (And with over 95 million lines, every bit of optimization is important)

I understand that my question is to do what SQL was not going to do. I just hope there is some sort of 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 requests (this is the one I use most of the time). He does not own the corresponding tables, so when I adapted the answer to my situation, I had to make the following changes:

USER_TAB_COLUMNS was supposed to be ALL_TAB_COLUMNS , and I had to add OWNER = '[OWNER]' to the request.

Answer 1

This is not a normal part of the database functionality. However, you are not the first person to ask for this, or anything like that.

The solution requires two things. The first is a data dictionary; the Oracle database does not support Reflection, but it does have a set of views that give us metadata about our database objects. In this case, we need user_tab_columns , which will give us the columns for this table. Secondly, it is dynamic SQL; this is the ability to collect an SQL query at runtime and then execute it. There are several ways to do this, but ref cursors are commonly used.

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

• name of the table you want to find
• primary key name of this table Column
• primary key value that you want to restrict
• the value you want to find.

This is no longer the case, so you may need to edit it to tidy up the result or 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’s harder to debug :) So it's nice to have the 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 looks like your database is not properly normalized. However, you can probably use the UNPIVOT command inside the subquery to do what you are trying to do.

Answer 3

My solution would be to use dictionary tables (USER_TAB_COLUMNS) to dynamically extract the name of all the NUMBER columns from your table and Dynamic SQL, because here I do not see how this could be avoided.

 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 will need to replace all the <variables> with the selected value.

For manual request, it works fine. However, the performance of this is probably poor, so do not run it against large datasets as they are.