NULL values just won't stop coming!

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

Other Forums

MS Access

NULL values just won't stop coming!

Author

Topic

chien_fu
Starting Member

16 Posts

Posted - 2007-12-19 : 06:27:43

I can't get the following statement to NOT return NULL values..

((SELECT IIF(Sum(Score) IS NULL, 0, Sum(Score)) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID) + (SELECT IIF(Sum(Opp_Score) IS NULL, 0, Sum(Opp_Score)) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) / (SELECT Count(ID) FROM Game_Schedule WHERE (Game_Schedule.T1_ID = standings.ID OR Game_Schedule.T2_ID = standings.ID) AND (Win=true OR Loss=true OR Tie=true)) AS FixedItGPG,

IIF(FixedItGPG IS NULL, 0, FixedItGPG) AS FixedItAgain

Even "FixedItAgain" variable is returning NULL values!! How does this make any sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 06:35:46

IS NULL is a SQL Server syntax.

Try ISNULL() function.
Or 0 + SUM(Score)
or SUM(Score + 0)

E 12°55'05.25"
N 56°04'39.16"

chien_fu
Starting Member

16 Posts

Posted - 2007-12-19 : 21:33:14

Brilliant! I love the concept...
Now if I could get it to work, I don't know where else I can add a zero to the equation.. And it is still returning a NULL value..

((SELECT Sum(Score + 0) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID) + (SELECT Sum(Opp_Score + 0) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) / ((SELECT Count(ID) FROM Game_Schedule WHERE (Game_Schedule.T1_ID = standings.ID OR Game_Schedule.T2_ID = standings.ID) AND (Win=true OR Loss=true OR Tie=true)) + 0) AS GPG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 21:39:46

You are never selecting from the standings table.
Do you care to show us the COMPLETE query?

E 12°55'05.25"
N 56°04'39.16"

chien_fu
Starting Member

16 Posts

Posted - 2007-12-19 : 21:42:10

I think I'm trying to divide: zero/zero in some cases... is that okay? I assume the answer to that is no, and in that case, how do I get around that?

chien_fu
Starting Member

16 Posts

Posted - 2007-12-19 : 22:17:57

I don't think you want to see it... it's pretty ugly, some of the stuff I am not using, and am just fussing around with it for testing.

strSQL="SELECT standings.ID, standings.T_Name, " & _
"(SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Win = true)+(SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Loss = true) AS WinNum, " & _
"(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Win = true)+(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Loss = true) AS LossNum, " & _
"(SELECT Count(Tie) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID OR Game_Schedule.T1_ID = standings.ID AND Tie = true) AS TieNum, " & _
"((SELECT Sum(Score) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID) + (SELECT Sum(Opp_Score) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) / (SELECT IIF(Count(ID) IN (0, NULL), 1, Count(ID)) FROM Game_Schedule WHERE (Game_Schedule.T1_ID = standings.ID OR Game_Schedule.T2_ID = standings.ID) AND (Win=true OR Loss=true OR Tie=true)) AS GPG, " & _
"((SELECT Sum(Opp_Score + 0) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(Score + 0) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) / ((SELECT Count(ID) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID OR Game_Schedule.T2_ID = standings.ID AND (Win=true OR Loss=true OR Tie=true)) + 0) AS GA, " & _
"((SELECT Sum(PP_1) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(Opp_PP_1) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) / " & _
"((SELECT Sum(PP_2) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(Opp_PP_2) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) AS PP, " & _
"(((SELECT Sum(Opp_PP_2) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(PP_2) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) - " & _
"((SELECT Sum(Opp_PP_1) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(PP_1) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID))) / " & _
"((SELECT Sum(Opp_PP_2) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID)+(SELECT Sum(PP_2) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID)) AS PK, " & _
"(SELECT Count(ID) FROM Game_Schedule WHERE (Game_Schedule.T1_ID = standings.ID OR Game_Schedule.T2_ID = standings.ID) AND (Win=true OR Loss=true OR Tie=true)) AS GamesPlayed, " & _
"standings.T_Tier " & _
"FROM standings " & _
"WHERE standings.T_Tier = " & where & " " & _
"GROUP BY standings.ID, standings.T_Name, 11, standings.T_Tier " & _
"ORDER BY " & sortby & ", standings.T_Name;"

All I care about right now are the values of GPG and GA (in blue)
Yes, this code is probably sloppy as all hell, I'm a newbie at SQL and am in way over my head but have committed now..

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-20 : 08:29:21

I suggest you use Nz() instead of Sum or IsNull...

More info here: http://msdn2.microsoft.com/en-us/library/aa172237.aspx

George
<3Engaged!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 08:45:53

Instead of all that awful subqueries which kills your performance, try to rethink and use derived tables instead like this

SELECT		s.ID,
		s.T_Name,
		Home.Wins + Away.Loss
		s.T_Tier 
FROM		Standings AS s
LEFT JOIN	(
			SELECT		T1_ID,
					SUM(CASE WHEN Win = 1 THEN 1 ELSE 0 END) AS Wins,
					SUM(CASE WHEN Loss = 1 THEN 1 ELSE 0 END) AS Loss
			FROM		Game_Schedule
			GROUP BY	T1_ID
		) AS Home ON Home.T1_ID = s.ID
LEFT JOIN	(
			SELECT		T2_ID,
					SUM(CASE WHEN Win = 1 THEN 1 ELSE 0 END) AS Wins,
					SUM(CASE WHEN Loss = 1 THEN 1 ELSE 0 END) AS Loss
			FROM		Game_Schedule
			GROUP BY	T2_ID
		) AS Away ON Away.T2_ID = s.ID
WHERE		s.T_Tier = @Where
GROUP BY	s.ID,
		s.T_Name,
		11,
		s.T_Tier
ORDER BY	@SortBy,
		s.T_Name

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources