OraFAQ Forum: SQL & PL/SQL » To Display Preferred Column

Home » SQL & PL/SQL » SQL & PL/SQL » To Display Preferred Column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To Display Preferred Column [message #322299]

Fri, 23 May 2008 01:07

ramya29p
Messages: 146
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?

Report message to a moderator

Re: To Display Preferred Column [message #322306 is a reply to message #322299]

Fri, 23 May 2008 01:25

Frank
Messages: 7901
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.

Report message to a moderator

Re: To Display Preferred Column [message #322501 is a reply to message #322299]

Fri, 23 May 2008 16:03

Littlefoot
Messages: 21825
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".

Report message to a moderator

Re: To Display Preferred Column [message #322504 is a reply to message #322299]

Fri, 23 May 2008 17:18

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: To Display Preferred Column [message #322523 is a reply to message #322504]

Sat, 24 May 2008 01:32

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: To Display Preferred Column [message #322524 is a reply to message #322523]

Sat, 24 May 2008 01:52

Frank
Messages: 7901
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.

Report message to a moderator

Previous Topic:	Grouping a sum inside a WHERE clause
Next Topic:	view creation error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 09 17:36:38 CDT 2025