HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #222982] |
Wed, 07 March 2007 02:13  |
cibuv
Messages: 3 Registered: March 2007 Location: Thiruvananthapuram
|
Junior Member |

|
|
Dear friends,
How can we select not null columns from a table ?
eg:
Table1 is having 3 columns namely col1,col2 and col3.
All the columns can have null values.
If the col1,col2 are having not null values i have to get only this columns in the query. How can I ?
Please help.
|
|
|
|
|
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #222999 is a reply to message #222994] |
Wed, 07 March 2007 02:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Why would you want this?
You are in for some heavy, errorprone sql if you really want this. Add to this the fact that your query cannot be used in any subsequent steps, and I can only conclude that this sounds like a bad idea.
SQL queries (normally) have a fixed number of columns.
|
|
|
|
|
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #223510 is a reply to message #222982] |
Fri, 09 March 2007 04:35   |
|
first we have to find columns which are not null columns
i.e.., from user_tab_cols dict table ok
declare
cursor col_cur is
select column_name
from user_tab_cols
where table_name ='employees';
v_count number:=0;
stmt varchar2(1000) := 'select';
begin
for rec in col_cur loop
execute immediate 'select count(*)
from employees
where'||rec.column_name||'is not null';
if col_cur =0 then
stmt :=stmt|| rec.column_name ||',';
end if
end loop;
stmt := rtrim ( stmt, ',') ||' from employees'
dbms_output.put_line(stmt);
end ;
|
|
|
|
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #223548 is a reply to message #222982] |
Fri, 09 March 2007 07:21  |
MarcL
Messages: 455 Registered: November 2006 Location: Connecticut, USA
|
Senior Member |
|
|
I'm going to do some mind reading here, and I may be way off base.
The OP mentioned summing of columns, I'm guessing there was an issue with trying to sum a Null column.
Would something like this suit your needs ?
select sum(nvl(col1,0) + nvl(col2,0) + nvl(col3,0)) as col_total from table.
Granted this would give a 0 for col3, but probably a lot better than trying to go through those hoops of excluding the column.
[Updated on: Fri, 09 March 2007 07:23] Report message to a moderator
|
|
|