Home » SQL & PL/SQL » SQL & PL/SQL » Filtering Column (XP, Oracle 9i)
Filtering Column [message #333770] Mon, 14 July 2008 07:18 Go to next message
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 Smile
Re: Filtering Column [message #333777 is a reply to message #333770] Mon, 14 July 2008 07:32 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look for IS NULL and IS NOT NULL
Re: Filtering Column [message #333798 is a reply to message #333770] Mon, 14 July 2008 08:06 Go to previous messageGo to next message
Frank
Messages: 7880
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 #333799 is a reply to message #333798] Mon, 14 July 2008 08:08 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Yep, I mis read the original post and swapped row for column Sad
Re: Filtering Column [message #333951 is a reply to message #333798] Tue, 15 July 2008 00:35 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
I have columns wich do not have any value at all. I want to hide these columns. As i have many columns like this i dont want to manually do it rather i want to select these columns if anyvalue is present.

[Updated on: Tue, 15 July 2008 00:43]

Report message to a moderator

Re: Filtering Column [message #333958 is a reply to message #333951] Tue, 15 July 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to post an example of what you want.

Regards
Michel
Re: Filtering Column [message #333992 is a reply to message #333958] Tue, 15 July 2008 02:38 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
please see the attachment.

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).
  • Attachment: example.txt
    (Size: 0.72KB, Downloaded 146 times)
Re: Filtering Column [message #333994 is a reply to message #333992] Tue, 15 July 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use attachment.
Post HERE you data and the result chart you want with them.
Don't forget to format as speficied in forum guide.

Regards
Michel
Re: Filtering Column [message #334005 is a reply to message #333994] Tue, 15 July 2008 03:13 Go to previous messageGo to next message
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 #334009 is a reply to message #334005] Tue, 15 July 2008 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In what piece of client software do you want this query to run?
Re: Filtering Column [message #334026 is a reply to message #334005] Tue, 15 July 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what happens if some rows have data in some columns and others don't have data in the same ones?

Regards
Michel
Re: Filtering Column [message #334029 is a reply to message #334009] Tue, 15 July 2008 04:25 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
I want to query in sqlplus
Re: Filtering Column [message #334033 is a reply to message #334026] Tue, 15 July 2008 04:32 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
those columns do not have any data in any rows. If they have data i want them to be displayed.

[Updated on: Tue, 15 July 2008 04:32]

Report message to a moderator

Re: Filtering Column [message #334071 is a reply to message #334033] Tue, 15 July 2008 06:47 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
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 Smile
Previous Topic: Calculate 1 week before given date
Next Topic: Can we write two "Insert into.." on a single table
Goto Forum:
  


Current Time: Tue Dec 06 12:29:46 CST 2016

Total time taken to generate the page: 0.15042 seconds