Filtering Column [message #333770] |
Mon, 14 July 2008 07:18  |
yoonus.it@gmail.com
Messages: 109 Registered: June 2007 Location: kuwait
|
Senior Member |
|
|
Hi,
I have a table with many fields. I want to display columns of this table if data is present. If the column is empty i dont want to display that column at all.
Is there a way to filter colmns this way?
Looking forward to your supporting
Thank You 
|
|
|
|
Re: Filtering Column [message #333798 is a reply to message #333770] |
Mon, 14 July 2008 08:06   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Oh dear..
Reminds me of a topic some while back. Someone asked for the same thing. Turned out into a big discussion.
Please be more specific in what you want: do you want to hide a field when ALL records have a null value for that column, or do you want to hide it on a per-record base?
If you want it per record, then how are you going to know which column is which, as the number of columns differs per record.
|
|
|
|
|
|
|
|
Re: Filtering Column [message #334005 is a reply to message #333994] |
Tue, 15 July 2008 03:13   |
yoonus.it@gmail.com
Messages: 109 Registered: June 2007 Location: kuwait
|
Senior Member |
|
|
ID SO_DATE DESC REMARKS START_D COMPLED AMOUN
5022984 10-Apr-08 10-Apr-08
5023040 24-Apr-08 24-Apr-08
5023073 30-Apr-08 30-Apr-08
5023096 4-May-08 4-May-08
5023106 4-May-08 4-May-08
5023108 4-May-08 4-May-08
5023127 5-May-08 5-May-08
5023150 11-May-08 11-May-08
5023158 13-May-08 13-May-08
Columns With Data Columns Without Data
ID DESC
SO_DATE REMARKS
START_D
COMPLED AMOUN
EXPECTING RESULT
ID SO_DATE COMPLED
5022984 10-Apr-08 10-Apr-08
5023040 24-Apr-08 24-Apr-08
5023073 30-Apr-08 30-Apr-08
5023096 4-May-08 4-May-08
5023106 4-May-08 4-May-08
5023108 4-May-08 4-May-08
5023127 5-May-08 5-May-08
5023150 11-May-08 11-May-08
5023158 13-May-08 13-May-08
In this example there are 3 columns with data and 4 without data. What I want to do actually is to select columns with data and display. So in the result i am expecting these three columns(id,so_date,compled).
|
|
|
|
|
|
|
Re: Filtering Column [message #334071 is a reply to message #334033] |
Tue, 15 July 2008 06:47   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Why?
If there are so many columns, what difference does it make if some empty columns are displayed or not.
You'd have to go through quite some trouble; probably executing your query twice, thereby stressing your database unnecessarily, in order to get this.
|
|
|
Re: Filtering Column [message #334111 is a reply to message #334033] |
Tue, 15 July 2008 08:12   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Run this script in SQL*Plus
SQL> create table test_0056 (col_1 varchar2(30), col_2 varchar2(30), col_3 varchar2(30));
insert into test_0056 values ('A',null,'B');
insert into test_0056 values ('C',null,'D');
VARIABLE c_cursor REFCURSOR
declare
v_sql varchar2(4000);
cursor c_got_data is
SELECT count(col_1) col_1
,count(col_2) col_2
,count(col_3) col_3
FROM test_0056;
r_got_data c_got_data%rowtype;
begin
open c_got_data;
fetch c_got_data into r_got_data;
close c_got_data;
v_sql := 'SELECT ';
if r_got_data.col_1 > 0 then
v_sql:= v_sql ||' COL_1,';
end if;
if r_got_data.col_2 > 0 then
v_sql:= v_sql ||' COL_2,';
end if;
if r_got_data.col_3 > 0 then
v_sql:= v_sql ||' COL_3,';
end if;
if v_sql = 'SELECT ' then
v_sql := v_sql ||' null';
else
v_sql := rtrim(v_sql,',');
end if;
v_sql := v_sql ||' FROM test_0056';
open :c_cursor FOR v_sql;
end;
/
print c_cursor;
Expected Results
COL_1 COL_3
------------------------------ ------------------------------
A B
C D
|
|
|
Re: Filtering Column [message #336621 is a reply to message #333770] |
Mon, 28 July 2008 07:24  |
yoonus.it@gmail.com
Messages: 109 Registered: June 2007 Location: kuwait
|
Senior Member |
|
|
So There is no simple way. I just dont want to waste my time by checking names of each column with null value thats how this idea came up in my mind. You might be wondering why i am not deleting this columns,i have only select access on these columns thats why i was searching for an easier way.
Thanks alot 
|
|
|