Filtering null record via proc sql or view

• maxcoder1@gmail.com

  Say Hey Kid

  Points: 684

  More actions

  October 4, 2011 at 3:23 am

  #258476

  Hi Folks,

  Kağıt Cinsi Sarı Yeşil Mavi

  Kalın 4 0 5

  İnce 2 0 3

  or

  Kağıt Cinsi Sarı Yeşil Mavi

  Kalın 4 0 5

  İnce 0 0 3

  I want to filter via proc sql or view to records which was returned 'null' or 'zero' value.

  My question is:If the zeros/nulls are in different columns on different rows,I want to hide the column.

  Result:

  Kağıt Cinsi Mavi

  Kalın 5

  İnce 3

  Thanks in advance.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 4, 2011 at 3:35 am

  #1390020

  This is something that's best done in the presentation layer. I don't know any way of doing it in the database layer without using some horrible dynamic SQL.

  John

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 4, 2011 at 3:48 am

  #1390032

  +1.

  However, you could calculate the SUM on the whole column in advance and use the sum to decide whether displaying or hiding the column:

  DECLARE @testData TABLE (

  [Kagit Cinsi] nvarchar(10),

  [Sari] int,

  [Yesil] int,

  [Mavi] int

  )

  INSERT INTO @testData VALUES (N'Kalin', 4, 0, 5)

  INSERT INTO @testData VALUES (N'Ince', 0, 0, 3)

  SELECT [Kagit Cinsi],

  [Sari],

  SUM([Sari]) OVER() AS [SUM_Sari],

  [Yesil],

  SUM([Yesil]) OVER() AS [SUM_Yesil],

  [Mavi],

  SUM([Mavi]) OVER() AS [SUM_Mavi]

  FROM @testData

  And then, on the app side:

  -- pseudo code --

  if SUM_Mavi > 0 then

  display column("Mavi")

  else

  hide column("Mavi")

  end

  Hope this helps

  Gianluca

  -- Gianluca Sartori