Query for particular integer value from multiple columns with number datatype

Question

I try to search a number from multiple columns (datatype number), but get ORA-01722: invalid number error.

My Query:

SELECT *
 FROM CAMPAIGN
WHERE 1481125 IN (select column_name
                    from all_tab_columns
                   where table_name = 'CAMPAIGN'
                     AND data_type = 'NUMBER');

What is wrong with it?

Answer 1

You are comparing your number 1481125 with the names of the each column, not the values of each column in your table.

To go from a column's name (from dba_tab_columns) to the values in that column, you need to use some form of dynamic SQL. Here's a relatively simple example:

DECLARE
  -- Since I don't have your CAMPAIGN table or data, I'm using DBA_OBJECTS in it's place.
  l_table_name      VARCHAR2 (30) := 'DBA_OBJECTS';
  l_search_number   NUMBER := 20;                                                             -- 1481125 in your example
  l_record          dba_objects%ROWTYPE;
  l_sql             VARCHAR2 (32000);
  l_column_number   NUMBER := 0;
  l_cur             SYS_REFCURSOR;
BEGIN
  -- First: build dynamic SQL statement of the form:
  -- SELECT * FROM table_name WHERE
  -- ( ( col_name_a = 20 ) OR ( col_name_b = 20 ) OR ... )
  l_sql   := 'SELECT * FROM dba_objects WHERE ( ';

  FOR r_number_column IN (SELECT column_name
                          FROM   dba_tab_columns
                          WHERE  table_name = l_table_name
                          AND    data_type = 'NUMBER'
                          ORDER BY column_id) LOOP
    IF l_column_number > 0 THEN
      l_sql   := l_sql || ' OR ';
    END IF;

    l_column_number   := l_column_number + 1;
    l_sql             := l_sql || '(' || r_number_column.column_name || ' = ' || l_search_number || ')';
  END LOOP;

  IF l_column_number = 0 THEN
    -- No number columns in table, so there should be no matches
    l_sql   := l_sql || ' 1=0';
  END IF;

  l_sql   := l_sql || ')';

  DBMS_OUTPUT.put_line (l_sql);

  OPEN l_cur FOR l_sql;

  LOOP
    FETCH l_cur INTO   l_record;

    EXIT WHEN l_cur%NOTFOUND;
    DBMS_OUTPUT.put_line ('Object Name ' || l_record.object_name || ' has search number ' || l_search_number);
  END LOOP;
END;

Answer 2

Your query is:

SELECT * FROM CAMPAIGN WHERE 1481125 IN 
(select column_name from all_tab_columns where table_name = 'CAMPAIGN' AND data_type='NUMBER')

Breaking that down we have:

SELECT * FROM CAMPAIGN WHERE 1481125 IN (<a set of numbers>)

and the subquery:

select column_name from all_tab_columns
where table_name = 'CAMPAIGN' 
AND data_type='NUMBER'

That subquery is going to return a list of column names e.g.

CAMPAIGN_COUNT
CAMPAIGN_ID
CAMPAIGN_NUMBER_OF_SOMETHINGS

Your query is thus equivalent to:

SELECT * FROM CAMPAIGN WHERE 1481125 IN 
   ('CAMPAIGN_COUNT', 'CAMPAIGN_ID', 'CAMPAIGN_NUMBER_OF_SOMETHINGS')

You can see why you would get the ORA-01722 error there?

You would need to write dynamic SQL to achieve your aim.