To retrieve not null values from all the coloumns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

To retrieve not null values from all the coloumns

Author

Topic

Alisha26
Starting Member

19 Posts

Posted - 2011-01-28 : 01:26:59

Hello All Can you please help me out with this query

I have a table it contains below columns and values

Customer_id Phone_number YSM_COMP_ID
189020 123-123-1234 AEFVEER
131490 null AESEERD
123460 112-12-123 null
null 123-122-123 AESERESE
122345 null NULL
null 122-122-123 AERASERE

like the above columns i have around 20 columns
Can anyone please tell me how to retrieve all the values from all the coloumns withoutnull values

Alisha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 01:30:47

do you mean the rows where none of the Cells (columns) are having NULL Value?

Alisha26
Starting Member

19 Posts

Posted - 2011-01-28 : 01:34:51

Yes Mik i mean the same i want to retrieve the coloumns which are having not null values

Alisha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 01:40:39

Ok na

Select Col1,col2,...,ColN
From TableName
Where
(Col1 is not null
and Col2 is not null
and Col3 is not null
.
.
.
And ColN is not null)

Will return the rows where none of the columns is NULL

Cheers!

Alisha26
Starting Member

19 Posts

Posted - 2011-01-28 : 02:03:29

Hi Mik thanks for the answer when i have ran the query

select comp_furi_name , phone_number
from
tablename
where
(
comp_furi_name is not null , phone_number is not null , ysm_comp_id is not null
)

but iam getting an error which i was unable to figure it out it is saying

ORA-00907 missing right paranthesis

CAN you please let me know where the problem is

Alisha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 02:06:14

select comp_furi_name , phone_number
from
tablename
where
(
comp_furi_name is not null , phone_number is not null , ysm_comp_id is not null
)

Replace the Red highlighted portion With AND

Alisha26
Starting Member

19 Posts

Posted - 2011-01-28 : 02:11:06

Hi Mik
i have tried that using both And Operator and OR operator but iam getting values which are common in three to four coloumns
but i want the values which are not null values which i can still see in my result set.

Alisha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 02:19:54

Let me confirm what i perceived so for is correct? You have a table with data similar to below

Declare @Table table (Col1 int, Col2 varchar(10),Col3 varchar(10))
Insert into @Table
Select *
from
(
SElect 1 Col1,NULL Col2,'MIK' Col3
Union All
SElect 2,'MIK',NULL
Union All
SElect 3,'MIK','MIK'
)a
Select * from @Table

And Want your query to return the Row Where all values are not NULL e.g. Col1=3?

Which you can achieve by running the below query:

Select * from @Table where (Col1 is not null and Col2 is not null and Col3 is not null)

if this is not what you need then try to explain with the help of this example!~

Cheers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 03:01:02

If you use ORACLE post your question at ORACLE forums

Madhivanan

Failing to plan is Planning to fail

Alisha26
Starting Member

19 Posts

Posted - 2011-01-28 : 03:10:29

Thanks Mik for your reply thanks a lot

Alisha

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 04:30:14

you're welcome !