Home » SQL & PL/SQL » SQL & PL/SQL » To Display Preferred Column
To Display Preferred Column [message #322299] Fri, 23 May 2008 01:07 Go to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi Can anyone give me suggestion?
SQL> select * from emp ;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
SQL> 

From the above table emp i want to display only first 4 columns(i.e EMPNO,ENAME,JOB,MGR).
is it possible to view the 4 columns without mentioning the column name in the select Query?
Re: To Display Preferred Column [message #322306 is a reply to message #322299] Fri, 23 May 2008 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is not possible.
Why would you want that? Is it really so much work to explicitly name the columns you need? If you are talking about large tables, you need to learn some intelligent copy-paste-macro stuff.
Re: To Display Preferred Column [message #322501 is a reply to message #322299] Fri, 23 May 2008 16:03 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
is it possible to view the 4 columns without mentioning the column name in the select Query?

Just wondering, how did you imagine this might work? How is Oracle supposed to know which columns you are interested in? Something like: close your eyes, take a deep breath, visualize 4 columns and say: "Dear Oracle, please, display only this, that, another this and another that columns when I say SELECT * FROM some_table".
Re: To Display Preferred Column [message #322504 is a reply to message #322299] Fri, 23 May 2008 17:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You cannot do this with a SQL statement alone. You cannot use the column_id in a select clause or use position the way that you can in an order by clause. However, you can use some dynamic sql to select the first four column_id's, as demonstrated below. But, it still leaves me wondering why. It is probably more trouble to do this than to type the four column names.

SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_sql  VARCHAR2 (32767);
  3  BEGIN
  4    v_sql := 'SELECT ';
  5    FOR r IN
  6  	 (SELECT column_name
  7  	  FROM	 user_tab_columns
  8  	  WHERE  table_name = 'EMP'
  9  	  AND	 column_id <= 4)
 10    LOOP
 11  	 v_sql := v_sql || r.column_name || ',';
 12    END LOOP;
 13    v_sql := RTRIM (v_sql, ',') || ' FROM emp';
 14    OPEN :g_ref FOR v_sql;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7369 SMITH      CLERK           7902
      7499 ALLEN      SALESMAN        7698
      7521 WARD       SALESMAN        7698
      7566 JONES      MANAGER         7839
      7654 MARTIN     SALESMAN        7698
      7698 BLAKE      MANAGER         7839
      7782 CLARK      MANAGER         7839
      7788 SCOTT      ANALYST         7566
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN        7698
      7876 ADAMS      CLERK           7788
      7900 JAMES      CLERK           7698
      7902 FORD       ANALYST         7566
      7934 MILLER     CLERK           7782

14 rows selected.

SCOTT@orcl_11g> 


Re: To Display Preferred Column [message #322523 is a reply to message #322504] Sat, 24 May 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can't be done with 1 SQL statements but with 2 and the help of SQL*Plus. Smile
SQL> col col1 format a5 new_value col1
SQL> col col2 format a5 new_value col2
SQL> col col3 format a5 new_value col3
SQL> col col4 format a5 new_value col4
SQL> select max(decode(column_id,1,column_name)) col1,
  2         max(decode(column_id,2,column_name)) col2,
  3         max(decode(column_id,3,column_name)) col3,
  4         max(decode(column_id,4,column_name)) col4
  5  from user_tab_columns
  6  where table_name='EMP'
  7    and column_id <= 4
  8  /
COL1  COL2  COL3  COL4
----- ----- ----- -----
EMPNO ENAME JOB   MGR

1 row selected.

SQL> select &col1, &col2, &col3, &col4 from emp;
EMPNO ENAME      JOB         MGR
----- ---------- --------- -----
 7369 SMITH      CLERK      7902
 7499 ALLEN      SALESMAN   7698
 7521 WARD       SALESMAN   7698
 7566 JONES      MANAGER    7839
 7654 MARTIN     SALESMAN   7698
 7698 BLAKE      MANAGER    7839
 7782 CLARK      MANAGER    7839
 7788 SCOTT      ANALYST    7566
 7839 KING       PRESIDENT
 7844 TURNER     SALESMAN   7698
 7876 ADAMS      CLERK      7788
 7900 JAMES      CLERK      7698
 7902 FORD       ANALYST    7566
 7934 MILLER     CLERK      7782

14 rows selected.

Of course, in a script, your put "set termout off" and "set termout on" around the first query and then it seems to magically find the first columns. Smile

Regards
Michel
Re: To Display Preferred Column [message #322524 is a reply to message #322523] Sat, 24 May 2008 01:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The requirement is just as flawed as the 'first rows without an explicit order by' we see a lot.
In a relational database, there is no such thing as the first few columns of a table.
Previous Topic: Grouping a sum inside a WHERE clause
Next Topic: view creation error
Goto Forum:
  


Current Time: Wed Dec 07 14:53:55 CST 2016

Total time taken to generate the page: 0.09766 seconds