Answered by:
Selecting not null values

Question
-
I have 5 columns and I'm trying to select all with out null values. Looking below how can I get all 3 records without the empty fileds?
id column1 column2 column3 column4 column5
123 test test test test
456 test test
789 test
select column1,column2,column3,column4,column5
from Table
Where column1<> '' or column2<> '' column3<>'' or column4 <>'' or column5<>''
I've tried a union all and joins but can't get all records when it gets to a column that is null.
Friday, September 19, 2008 1:13 PM
Answers
-
this doesn't help either but I figured out a way to do it. I just use more than one source for each task. It seems to work fine as long as I have the nolock option unchecked in my destination. Thanks for the help.Friday, September 19, 2008 3:03 PM
All replies
-
Code Snippet
select column1,column2,column3,column4,column5
from Table
Where isnull(column1,'')<> '' or isnull(column2,'')<> '' ......
Friday, September 19, 2008 1:17 PM -
To check if a value is NULL or not, you should use " column IS NOT NULL " or " column IS NULL ".
What is the out put you are expecting? Are you looking for rows where atleast one column is NOT NULL? or are you looking for columns where all the 5 columns are NOT NULL?
Friday, September 19, 2008 1:19 PM -
I don't want anything if the column is null. I just want non null columns for each record.
Friday, September 19, 2008 1:38 PM -
mr4100 wrote: I don't want anything if the column is null. I just want non null columns for each record.
So,what 's your result by your example data.
Friday, September 19, 2008 1:41 PM -
well, after thinking about I don't think it's possible with what I'm trying to do. I'm using a dataflow task in ssis and what I want to do is use one source to insert only non null records into the db. I think I have to have a separate souce to not pick up the null values but I thought I could nest the queries together into one soure and use a multicast task to split the data up by the column names. This probably doesn't make sense but I think I need to have 5 separate dataflow tasks and just make the same query for each and for each column set my where clause to <> '' I DON'T KNOW AND TGIF!!!Friday, September 19, 2008 1:49 PM
-
Code Snippetcoalesce(column1,column2,column3,column4,column5) is not null;
Below one is very similar logic .
Code SnippetLeast(column1,column2,column3,column4,column5) is not null;
Friday, September 19, 2008 2:02 PM -
this doesn't help either but I figured out a way to do it. I just use more than one source for each task. It seems to work fine as long as I have the nolock option unchecked in my destination. Thanks for the help.Friday, September 19, 2008 3:03 PM