CODEWITHSUNDEEP
t-sqlreporting-services
Kyoujin
Kyoujin

Reputation: 324

If Exists allowing a null value through

I have a catch script that is meant to pick up when a sum comes out with a null value and insert a 0, which works fine on one query but not my current one, if I break down what code I am using

Declare @Period int = 5
SELECT A.MATTER_CODE,DATEPART(month,A.DATE_OPENED) As DateOpened,B.DEPTNAME,B.DEPTCODE 
INTO #TmpPREVYTD
FROM MATTER A
LEFT JOIN DEPT_MASTER B on A.DEPT_CODE = B.DEPTCODE
WHERE A.date_opened between DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, -1, GETDATE())), 120) and 
DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GETDATE())), 120)
ORDER BY DATE_OPENED

This pushes out the data I require for the query in the correct year and month

SELECT COUNT(*) As 'Fin',Dateopened Into #TmpPFin FROM #TmpPREVYTD where DEPTCODE = 'FIN' GROUP BY 
DATEOPENED

This counts every time a job relating to Finance is in the raw data.

SELECT SUM(FIN)As Fin INTO #PFIN FROM #TmpPFIN WHERE Dateopened Between 5 and @Period

This then sums up all the months which relate to the required set months, so in this example I just want it to count May only, which has nothing in it, thus why it is pushing a null value.

If EXISTS(Select Fin from #PFIN) 
GOTO TmpITD
Else insert into #PFIN (Fin) Values( 0) 
TmpITD:

Finally this is the catcher which should be finding the #PFin has a null value and inserting a 0, however what I think it is doing is going straight to TmpITD, as if I just run the insert statement it adds the 0.

so currently if I run the entire statement I keep getting a null value which means the report in the end comes out blank.

Am I missing something here as this exact same code works in other queries but not in this one, it would appear that perhaps it does exist somehow but with a null value, which has totally confused me.

EDIT: If I add something to the end of TmpITD it pushes that out so I know for sure now that the problem is the if Exists thinks it does exist when it is actually Null

Upvotes: 0

Views: 61

Answers (1)

BarneyL
BarneyL

Reputation: 1362

If your final table #PFIN has rows then it exists even if the values in it are NULL. A solution might be the following tweak to remove NULL rows and get a truly empty data set:

If EXISTS(Select Fin from #PFIN WHERE Fin IS NOT NULL) 
GOTO TmpITD
Else insert into #PFIN (Fin) Values( 0) 
TmpITD:

Alternatively as aggrigates can return 0 when you group a set of NULLS you may want to exclude 0 values too:

If EXISTS(Select Fin from #PFIN WHERE Fin <> 0) 
GOTO TmpITD
Else insert into #PFIN (Fin) Values( 0) 
TmpITD:

Upvotes: 1

Related Questions