Thanks for the question, chirayu.
Asked: August 16, 2002 - 11:23 pm UTC
Last updated: August 16, 2002 - 11:23 pm UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
Dear Tom,
I have a empmast table having some 100 odd columns in it.
Many of the table column are empty for most of the employees.
Can I write a sinqle query in such a way that only the columns
that have values in it are displayed.
for eg. say for empno = 100.
Of the 100 columns that I have the details availabe to me
are only of empno, firstnm, middlenm, surname, marital_status columns.
The rest of the columns are null.
Here I would like to run a query in such a way that only these columns
are displayed for this empno. for an other empno there might be some
more details available. what i want is my query should dynamically
display all the columns who have some values in it.
Thanx in advance
chirayu sutaria
and Tom said...
There is no "query" that can do this (every row has a fixed number of columns -- the same as the row before it and the row after it)
This is a problem for the thing that prints the result set to deal with. In sqlplus, you can use this technique (which also has the nice effect of printing DOWN the page, great for wide results)
scott@ORA817DEV.US.ORACLE.COM> create or replace procedure print_table( p_query in varchar2, p_nulls in boolean default true )
2 AUTHID CURRENT_USER
3 is
4 l_theCursor integer default dbms_sql.open_cursor;
5 l_columnValue varchar2(4000);
6 l_status integer;
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 begin
10 execute immediate
11 'alter session set
12 nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
13
14 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
15 dbms_sql.describe_columns
16 ( l_theCursor, l_colCnt, l_descTbl );
17
18 for i in 1 .. l_colCnt loop
19 dbms_sql.define_column
20 (l_theCursor, i, l_columnValue, 4000);
21 end loop;
22
23 l_status := dbms_sql.execute(l_theCursor);
24
25 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
26 for i in 1 .. l_colCnt loop
27 dbms_sql.column_value
28 ( l_theCursor, i, l_columnValue );
29 if ( p_nulls OR l_columnValue is not null )
30 then
31 dbms_output.put_line
32 ( rpad( l_descTbl(i).col_name, 30 )
33 || ': ' ||
34 substr( l_columnValue, 1, 200 ) );
35 end if;
36 end loop;
37 dbms_output.put_line( '-----------------' );
38 end loop;
39 execute immediate
40 'alter session set nls_date_format=''dd-MON-rr'' ';
41 exception
42 when others then
43 execute immediate
44 'alter session set nls_date_format=''dd-MON-rr'' ';
45 raise;
46 end;
47 /
Procedure created.
scott@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
scott@ORA817DEV.US.ORACLE.COM> create table t ( x int, y int, z int );
Table created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, null, null );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, null );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1 );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( null, 1, null );
1 row created.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from t', FALSE );
X : 1
-----------------
X : 1
Y : 1
-----------------
X : 1
Y : 1
Z : 1
-----------------
Y : 1
-----------------
PL/SQL procedure successfully completed.
scott@ORA817DEV.US.ORACLE.COM>
Is this answer out of date? If it is, please let us know via a Comment