Multi-line SELECT statement not working. Why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Multi-line SELECT statement not working. Why?

Author

Topic

steve_benson
Starting Member

2 Posts

Posted - 2008-09-11 : 13:32:58

I've created a view in SQL 2000 to display a sum of values from multiple integer fields. It works when I'm only adding a couple columns together, but returns nothing when the SELECT statement creates more than one line. Here's the statement:

SELECT District_AUN, Building_AUN, Current_Grade, Class, Tutor_Fname, Tutor_Lname, PASecureID, Student_Lname, Student_Fname, Student_DOB,
Building_name,
([8_26_2008] + [8_27_2008] + [8_28_2008] + [8_29_2008] + [9_2_2008] + [9_3_2008] + [9_4_2008] + [9_5_2008] + [9_8_2008] + [9_9_2008] + [9_10_2008]
+ [9_11_2008] + [9_15_2008] + [9_16_2008] + [9_17_2008] + [9_18_2008] + [9_19_2008] + [9_22_2008] + [9_23_2008] + [9_24_2008] + [9_25_2008] + [9_26_2008])
/ 60 AS Window01
FROM dbo.student_fields

I've confirm that all of the fields I'm trying to add are integer fields. It doesn't error out, it just returns NULL even for the fields that have valid data. Does SQL 2000 need a special character at the end of the line or am I missing something else?

Thanks,
Steve

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-09-11 : 13:39:10

One of the columns contains a null


SELECT 
	District_AUN, 
	Building_AUN, 
	Current_Grade, 
	Class, 
	Tutor_Fname, 
	Tutor_Lname, 
	PASecureID, 
	Student_Lname, 
	Student_Fname, 
	Student_DOB, 
	Building_name, 
	(	
		IsNull([8_26_2008], 0) + IsNull([8_27_2008], 0) + IsNull([8_28_2008], 0) + 
		IsNull([8_29_2008], 0) + IsNull([9_2_2008], 0) + IsNull([9_3_2008], 0) + 
		IsNull([9_4_2008], 0) + IsNull([9_5_2008], 0) + IsNull([9_8_2008], 0) + 
		IsNull([9_9_2008], 0) + IsNull([9_10_2008], 0) + IsNull([9_11_2008], 0) + 
		IsNull([9_15_2008], 0) + IsNull([9_16_2008], 0) + IsNull([9_17_2008], 0) + 
		IsNull([9_18_2008], 0) + IsNull([9_19_2008], 0) + IsNull([9_22_2008], 0) + 
		IsNull([9_23_2008], 0) + IsNull([9_24_2008], 0) + IsNull([9_25_2008], 0) + IsNull([9_26_2008], 0)
	) / 60 AS Window01
FROM dbo.student_fields

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 13:40:29

Use the COALESCE function to handle the columns that can contain NULL data. This would be done for the columns involved with Window01.

You might also want to consider writing /60.0 so that you get decimal places. Otherwise, it'll return only whole numbers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_benson
Starting Member

2 Posts

Posted - 2008-09-11 : 13:58:46

Perfect! Worked like a charm. I added the '60.0' tip from Ms. Kizer.

Thanks to both of you for the quick help!
Steve

quote:

Originally posted by jhocutt

One of the columns contains a null


SELECT 
	District_AUN, 
	Building_AUN, 
	Current_Grade, 
	Class, 
	Tutor_Fname, 
	Tutor_Lname, 
	PASecureID, 
	Student_Lname, 
	Student_Fname, 
	Student_DOB, 
	Building_name, 
	(	
		IsNull([8_26_2008], 0) + IsNull([8_27_2008], 0) + IsNull([8_28_2008], 0) + 
		IsNull([8_29_2008], 0) + IsNull([9_2_2008], 0) + IsNull([9_3_2008], 0) + 
		IsNull([9_4_2008], 0) + IsNull([9_5_2008], 0) + IsNull([9_8_2008], 0) + 
		IsNull([9_9_2008], 0) + IsNull([9_10_2008], 0) + IsNull([9_11_2008], 0) + 
		IsNull([9_15_2008], 0) + IsNull([9_16_2008], 0) + IsNull([9_17_2008], 0) + 
		IsNull([9_18_2008], 0) + IsNull([9_19_2008], 0) + IsNull([9_22_2008], 0) + 
		IsNull([9_23_2008], 0) + IsNull([9_24_2008], 0) + IsNull([9_25_2008], 0) + IsNull([9_26_2008], 0)
	) / 60.0 AS Window01
FROM dbo.student_fields

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources