When no rows returned in subquery

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

When no rows returned in subquery

Author

Topic

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-16 : 17:34:25

Is there a way to handle when no row is returned in a subquery or query? Please help me how to handle the situation.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 17:56:24

Can you elaborate your requirement?

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-16 : 17:58:05

SELECT DISTINCT B.Location_Code,B.FY_Month,B.FY_Year,B.Fiscal_Month_Id,
(CASE WHEN (HP.Hires) IS NOT NULL THEN SUM(HP.Hires)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year) ELSE 0 END)AS Hires,
(CASE WHEN (HP.Separations_Voluntary) IS NOT NULL THEN SUM(HP.Separations_Voluntary)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year) ELSE 0 END) AS SepV,
(CASE WHEN (HP.Separations_Involuntary)IS NOT NULL THEN SUM(HP.Separations_Involuntary)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year) ELSE 0 END)AS SepInv,
(CASE WHEN (HP.Transfers_In)IS NOT NULL THEN SUM(HP.Transfers_In)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END)AS TIn,
(CASE WHEN (HP.Transfers_Out)IS NOT NULL THEN SUM(HP.Transfers_Out)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END) AS TOut,
(CASE WHEN (HP.Promotions_In)IS NOT NULL THEN SUM(HP.Promotions_In)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END) AS PIn,
(CASE WHEN (HP.Promotions_Out)IS NOT NULL THEN SUM(HP.Promotions_Out)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END)AS POut,
(CASE WHEN (HP.Leave_Of_Absence_In)IS NOT NULL THEN SUM(HP.Leave_Of_Absence_In)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END) AS LOAIn,
(CASE WHEN (HP.Leave_Of_Absence_Out)IS NOT NULL THEN SUM(HP.Leave_Of_Absence_Out)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END)AS LOAOut,
(CASE WHEN (HP.Other)IS NOT NULL THEN SUM(HP.Other)OVER(PARTITION BY B.Location_Code,B.FY_Month,B.Fiscal_Month_Id,B.FY_Year)ELSE 0 END) AS Other,
B.Total_Hours AS TotalHours
FROM
(SELECT DISTINCT t.BUId,t.BUCode,t.Location_Code,FisM.FY_Month,FisM.FY_Year,t.Location_Id,FisM.Fiscal_Month_Id,FisM.Total_Hours
FROM (Select DISTINCT BU.Business_Unit_Id AS BUId,BU.Business_Unit_Code AS BUCode,L.Location_Code,HP.Location_Id FROM Location L
INNER JOIN HeadCount_Planned HP ON L.Location_Id = HP.Location_Id AND L.Location_Code IN('1413A','0533B')
INNER JOIN Business_Unit BU ON BU.Business_Unit_Id = HP.Business_Unit_Id AND BU.Business_Unit_Code IN('US017','US313')
AND Planning_Period_Id = (Select Planning_Period_id FROM Planning_Period WHERE FY_Year = '2009'))t
CROSS JOIN Fiscal_Month FisM WHERE Fiscal_Month_Id BETWEEN (Select Fiscal_Month_Id FROM Fiscal_Month WHERE FY_Year = '2008' AND FY_Month = 'MAR')
AND (Select Max(Fiscal_Month_Id) FROM Fiscal_Month WHERE FY_Year = '2009')
)B
LEFT JOIN HeadCount_Planned HP
ON HP.Fiscal_Month_Id = B.Fiscal_Month_Id AND HP.Location_Id = B.Location_Id AND HP.Business_Unit_Id = B.BUId
AND HP.Planning_Period_Id = (Select Planning_Period_id FROM Planning_Period WHERE FY_Year = '2009' AND is_Current = 1)
ORDER BY B.Location_Code,B.FY_Year,B.FY_Month,B.Fiscal_Month_Id

When in query
(Select DISTINCT BU.Business_Unit_Id AS BUId,BU.Business_Unit_Code AS BUCode,L.Location_Code,HP.Location_Id FROM Location L
INNER JOIN HeadCount_Planned HP ON L.Location_Id = HP.Location_Id AND L.Location_Code IN('1413A','0533B')
INNER JOIN Business_Unit BU ON BU.Business_Unit_Id = HP.Business_Unit_Id AND BU.Business_Unit_Code IN('US017','US313')
AND Planning_Period_Id = (Select Planning_Period_id FROM Planning_Period WHERE FY_Year = '2009'))
returns no row, I get NULL but I need to still get the BU and Loc displayed but with 0 values.

Ohmslaw
Starting Member

11 Posts

Posted - 2009-01-16 : 18:00:03

IF NOT EXISTS(select field from tbl where col = @col)

or

select field from tbl where col = @col
IF @@ROWCOUNT = 0

Ohms...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 18:05:10

Can't you use ISNULL or COALESCE function to achieve that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 23:39:36

or if your intention is to perform aggregation use COUNT() or SUM() in subquery which will return 0 rather than NULL for no data.
But even if you get NULL you can convert it to 0 or other value using ISNULL() or COALESCE() as Sodeep suggested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources