select data of specific column from table where column names are returned by query

Question

I have written 2 separate queries

1)


SELECT COLUMN_NAME
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME =
       (SELECT DISTINCT UT.TABLE_NAME
          FROM USER_TABLES UT
         WHERE UT.TABLE_NAME = 'MY_TABLE')
   AND COLUMN_NAME NOT IN ('AVOID_COLUMN')

2)


    SELECT *
      FROM MY_TABLE MT
     WHERE MT.COL1 = '1'

The 1st query returns the names of all the columns except the one I want to avoid. The 2nd one returns data of all the columns from the table. Is there some way to merge these queries so that only those column's data is selected from the 2nd query, which are returned from the 1st query?

Thanks in advance

Check my answer to a very similar question asked this morning: http://stackoverflow.com/a/11241724/146325 — APC, Jun 28 '12 at 12:26

score 4 · Accepted Answer · answered Jun 28 '12 at 10:56

4

You'll have to use dynamic SQL for this (BTW, I got rid of the subselect for the USER_TABLES query - it's unnecessary):

var  cur refcursor
/
declare
  v_stmt varchar2(4000);
begin
  v_stmt := 'SELECT ';  
  for cur in (
    SELECT COLUMN_NAME
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME =
       'MY_TABLE'
    AND COLUMN_NAME NOT IN ('AVOID_COLUMN')
  ) 
  loop
    v_stmt := v_stmt || cur.column_name || ',';
  end loop;
  -- get rid of trailing ','
  v_stmt := regexp_replace(v_stmt, ',$', '');

  v_stmt := v_stmt || ' from my_table MT WHERE MT.COL1 = ''1''';
  dbms_output.put_line(v_stmt);
  open :cur for v_stmt;
end;

answered Jun 28 '12 at 10:56

Frank Schmitt

30,195
12
73
107

Thanks for your effort Frank. However, I was hoping if there was any way to to this in sql completely? – Rohan Jun 28 '12 at 11:02
1

@Rohan: Sorry, but I have to ask: can't you simply define a view? – archimede Jun 28 '12 at 11:06
I agree with archimede - a view would be the most simple solution. Apart from that - no, you cannot do it in (static) SQL, since the column list you want to select cannot change; therefore, you have to dynamically create the statement. But why don't you want to use PL/SQL? – Frank Schmitt Jun 28 '12 at 11:10
I was trying the query in this way so as to get a query that displays columns EXCEPT a column that I want to avoid. Usually we have to explicitly name the columns we want to display – Rohan Jun 28 '12 at 11:13
1

Just use a view - create view v_my_view as select from my_table, and query against that view. – Frank Schmitt Jun 28 '12 at 11:49

select data of specific column from table where column names are returned by query

1 Answers1