ignore data for null value

• pat-261672

  Hall of Fame

  Points: 3325

  More actions

  September 1, 2009 at 5:07 pm

  #225657

  Hi,

  i am trying to do union i get data with null values and i want do not want it to show in result

  such as:

  column1 col2 col3 col4 col5 col6

  test1 1 2 3 null 6

  test2 2 3 4 null 6

  test3 3 4 5 6 null

  test4 4 4 4 4 4

  I do not want to show revords with null values in any column i only want to only records with data in all column.

  How can i set that.

  Please help

  thanks

  Pat

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 1, 2009 at 7:57 pm

  #1047930

  I'd start by showing us what you have tried so far. hard to know what you are doing wrong in your code without seeing it.

• Kruti Kansara

  Hall of Fame

  Points: 3118

  More actions

  September 2, 2009 at 1:56 am

  #1047971

  There are plenty of ways to do so..

  - You can create Stored Procedure with cursor

  - You can create queries with subqueries

  - create queries with CASE

  did u try anything?

  "Don't limit your challenges, challenge your limits"

• arun.sas

  SSChampion

  Points: 11831

  More actions

  September 2, 2009 at 2:36 am

  #1047981

  Hi,

  Try this

  create table #temp1

  (

  id1 int null,

  id2 varchar(10) null,

  id3 int null,

  id4 int null

  )

  insert into #temp1

  select null,'TEST',null,null

  union all

  select 1,'REAL',2,3

  union all

  select 2,'REAL1',3,4

  union all

  select 1,null,2,3

  union all

  select 1,'TEST',2,null

  01)

  select * from #temp1

  where id1 is not null

  and id2 is not null

  and id3 is not null

  and id4 is not null

  02)

  select * from #temp1

  where (cast((id1+id3+id4)as varchar)+id2) is not null