locked
Selecting not null values RRS feed

  • 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,'')<> '' ......

     

     

     

    Is this your meaning?
    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 Snippet
    coalesce(column1,column2,column3,column4,column5) is not null;

     

     

    Below one is very similar logic .

     

    Code Snippet
    Least(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