CODEWITHSUNDEEP
sqloracle-database
Venkat
Venkat

Reputation: 109

Oracle SQL to Check a value against a specific list of columns

I got the SQLfiddle link copied below for my required.

CREATE TABLE TAB1
(COL1 NUMBER,
 COL2 NUMBER,
 COL3 NUMBER,
 COL4 NUMBER);

 INSERT INTO TAB1 VALUES(1,2,3,2);
 INSERT INTO TAB1 VALUES(1,2,1,2);
 INSERT INTO TAB1 VALUES(1,2,2,2);
 INSERT INTO TAB1 VALUES(1,2,2,2);

I want to check if columns col2, col3,col4 has the value 2. I have the below sql to achieve my requirement. But the problem is I have to check the same for 300 columns. Table is already designed like this by the developer as per incoming data file and I can't change anything now. Is there a better way of doing this other than using the below SQL with 300 column names in ALL(col1..col300)?

SELECT *
  FROM TAB1
 WHERE 2 = ALL (COL2,COL3,COL4);

SQLfiddle link is below with expected output

http://www.sqlfiddle.com/#!4/fbc00/3/0

Appreciate the responses. Thank you

Upvotes: 0

Views: 805

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

As The Impaler said, dynamic SQL could help. If you can't fix that design, see if something like this helps: function accepts search value as a parameter, uses query you already wrote and returns refcursor.

SQL> create or replace function f_ok (par_search_value in number)
  2    return sys_refcursor
  3  is
  4    l_str  varchar2(32000) := 'select * From tab1 where ' || par_search_value ||' = all(';
  5    l_rc   sys_refcursor;
  6  begin
  7    for cur_r in (select column_name
  8                  from user_tab_columns
  9                  where table_name = 'TAB1'
 10                    and column_id > 1
 11                  order by column_id
 12                 )
 13    loop
 14      l_str := l_str || cur_r.column_name || ', ';
 15    end loop;
 16    l_str := rtrim(l_str, ', ') || ')';
 17    open l_rc for l_str;
 18    return l_rc;
 19  end;
 20  /

Function created.

Testing:

SQL> select * from tab1;

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         1          2          3          2
         1          2          1          2
         1          2          2          2
         1          2          2          2

SQL> var rc refcursor
SQL> exec :rc := f_ok(2);

PL/SQL procedure successfully completed.

SQL> print rc

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         1          2          2          2
         1          2          2          2

SQL>

Upvotes: 1

Related Questions