Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO SELECT NOT NULL COLUMNS FROM A TABLE
HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #222982] Wed, 07 March 2007 02:13 Go to next message
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 #222986 is a reply to message #222982] Wed, 07 March 2007 02:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could you please give an example of what you mean?
Do you only want rows where all three columns are not null? (easy: col1 is not null and col2 is not null and col3 is not null)
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #222994 is a reply to message #222986] Wed, 07 March 2007 02:31 Go to previous messageGo to next message
cibuv
Messages: 3
Registered: March 2007
Location: Thiruvananthapuram
Junior Member

Dear sir,
Here is the example.

Table Name : table1 (having 3 columns COL1,COL2 & COL3)

COL1 COL2 COL3
---- ---- ----
235 541
343
343
566 455

Here i need only SUM(COL1) & SUM(COL2) as a query.
I dont want COL3 because it does have any data.

Please help

Cibu

Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #222999 is a reply to message #222994] Wed, 07 March 2007 02:43 Go to previous messageGo to next message
Frank
Messages: 7880
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 #223053 is a reply to message #222994] Wed, 07 March 2007 07:02 Go to previous messageGo to next message
cibuv
Messages: 3
Registered: March 2007
Location: Thiruvananthapuram
Junior Member

sir,
then
i would like to have my column names in the select query to be dynamic. How can I pass my variable to a select query.
eg :
variable V1 value is 'COL1, COL2'

after passing this variable the query value should be like
SELECT COL1,COL2 FROM TABLE

could u please help me

cibu
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #223074 is a reply to message #223053] Wed, 07 March 2007 08:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To this I could reply exactly the same...

What you want can be done using dynamic sql, but, again, you don't want to go there. Better recheck your requirements and ask yourself (or the designer/analyst) why it should be this way.
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #223510 is a reply to message #222982] Fri, 09 March 2007 04:35 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

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 #223538 is a reply to message #223510] Fri, 09 March 2007 06:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
and what if you have a million employees?
Could take a day or two..

Question remains: why?
Re: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE [message #223548 is a reply to message #222982] Fri, 09 March 2007 07:21 Go to previous message
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

Previous Topic: SQL Query
Next Topic: Roles and Objects
Goto Forum:
  


Current Time: Wed Dec 07 06:47:09 CST 2016

Total time taken to generate the page: 0.26785 seconds