Any way to suppress NULL value row not to display

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

General SQL Server Forums

New to SQL Server Programming

Any way to suppress NULL value row not to display

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-01 : 15:50:06

I have a query which is combination of 2 queries (queries where part of where condition varies). It give me correct result but due to the fact that i have combined both the queries as one (because I need it so) it display NULL value in those additional rows. IS there anyway to suppress the display of this NULL valued rows.

Query USed
-----------

SELECT rnk.Rolling_Plan_Display AS RankName
,
CASE
WHEN (ha.Fiscal_Month_Id = (SELECT MIN(Fiscal_Month_Id)FROM Fiscal_Month WHERE FY_Year = '2008')) THEN Beginnig_Balance

END AS BeginningBalance
-- , Ending_Balance AS BeginningBalance
, SUM(Hires) OVER(PARTITION BY rnk.Rolling_Plan_Display) AS Hires
, SUM(Separations_Voluntary) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS SeparationVoluntary
, SUM(Separations_InVoluntary) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS SeparationInvoluntary
, SUM(Transfers_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS TransfersIn
, SUM(Transfers_Out) OVER(PARTITION BY rnk.Rolling_Plan_Display) AS TransfersOut
, SUM(Promotions_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS PromotionsIn
, SUM(Promotions_Out) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS PromotionsOut
, SUM(Leave_Of_Absence_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS UnpaidLeaveIn
, SUM(Leave_Of_Absence_Out) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS UnpaidLeaveOut

FROM HeadCount_Actual ha, Rank rnk
WHERE ha.Business_Unit_Id =
(SELECT Business_Unit_Id
FROM Business_Unit
WHERE Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND ha.Location_Id =
(SELECT Location_Id
FROM Location
WHERE Location_Code = '0010L'
AND Location_Name = 'New York, NY')
AND ha.Fiscal_Month_Id BETWEEN
(SELECT MIN(Fiscal_Month_Id)
FROM Fiscal_Month
WHERE FY_Year = '2008')
AND
(SELECT MAX(Fiscal_Month_Id)
FROM Fiscal_Month
WHERE FY_Year = '2008')
AND rnk.Rank_Id = ha.Rank_Id
AND rnk.Is_Used_For_Planning = 1

Result IS
------------

Executive Director 4 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0
Executive Director NULL 0 1 0 0 0 0 0 0 0

Look at that additional rows for the Executive Director displayed with NULL. Is there a way to suppress that display.

Actually in the above query
AND ha.Fiscal_Month_Id BETWEEN
(SELECT MIN(Fiscal_Month_Id)
FROM Fiscal_Month
WHERE FY_Year = '2008')
AND
(SELECT MAX(Fiscal_Month_Id)
FROM Fiscal_Month
WHERE FY_Year = '2008')

Portion for Beginng Balance column should be for specific month but since i need this resultset together I tried combining this result.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 16:50:29

I don't really understand your whole query, but to suppress the rows with NULL-Value in BeginningBalance you can wrap the whole query as a derived table and then filter it like this:

SELECT * FROM
(
SELECT rnk.Rolling_Plan_Display AS RankName 
, 
CASE
WHEN (ha.Fiscal_Month_Id = (SELECT MIN(Fiscal_Month_Id)FROM Fiscal_Month WHERE FY_Year = '2008')) THEN Beginnig_Balance

END AS BeginningBalance
-- , Ending_Balance AS BeginningBalance
, SUM(Hires) OVER(PARTITION BY rnk.Rolling_Plan_Display) AS Hires
, SUM(Separations_Voluntary) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS SeparationVoluntary
, SUM(Separations_InVoluntary) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS SeparationInvoluntary
, SUM(Transfers_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS TransfersIn
, SUM(Transfers_Out) OVER(PARTITION BY rnk.Rolling_Plan_Display) AS TransfersOut
, SUM(Promotions_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS PromotionsIn
, SUM(Promotions_Out) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS PromotionsOut
, SUM(Leave_Of_Absence_In) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS UnpaidLeaveIn
, SUM(Leave_Of_Absence_Out) OVER (PARTITION BY rnk.Rolling_Plan_Display) AS UnpaidLeaveOut

FROM HeadCount_Actual ha, Rank rnk
WHERE ha.Business_Unit_Id =
(SELECT Business_Unit_Id
FROM Business_Unit
WHERE Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND ha.Location_Id = 
(SELECT Location_Id
FROM Location
WHERE Location_Code = '0010L'
AND Location_Name = 'New York, NY')
AND ha.Fiscal_Month_Id BETWEEN
(SELECT MIN(Fiscal_Month_Id) 
FROM Fiscal_Month
WHERE FY_Year = '2008') 
AND 
(SELECT MAX(Fiscal_Month_Id) 
FROM Fiscal_Month
WHERE FY_Year = '2008') 
AND rnk.Rank_Id = ha.Rank_Id
AND rnk.Is_Used_For_Planning = 1
)t
WHERE BeginningBalance IS NOT NULL