Evaluating Derived Columns within a Select

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

Evaluating Derived Columns within a Select

Author

Topic

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 06:42:45

Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!

The original Access SQL appears below:

SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 06:54:12

Try this

DECLARE @Input INT

SELECT @Input = 2004


SELECT		@Input AS [input],
		ResearchStudent.Department,
		ResearchStudent.DateAwarded,
		ResearchStudent.StudentNumber,
		Person.Forenames AS fore,
		Person.Surname AS Sur,
		ResearchStudent.Mode,
		ResearchStudent.RegistrationDate,
		StudentExamination.Decision,
		IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
		ResearchStudent.EnrollmentCategory,
		qryStudentSuspGroup.Suspension
FROM		ResearchStudent
LEFT JOIN	Person ON ResearchStudent.ResearchStudentID = Person.PersonID
LEFT JOIN	qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID
LEFT JOIN	StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE 		Year([DateAwarded]) BETWEEN @Input AND @Input + 1
		AND	CASE
				WHEN Year([DateAwarded]) = @Input AND Month([DateAwarded]) > 8 THEN 1
				WHEN Year([DateAwarded]) <> @Input AND Month([DateAwarded]) < 9 THEN 1
				ELSE 0
			END = 1
ORDER BY	ResearchStudent.Department,
		ResearchStudent.Mode,
		([DateAwarded] - [RegistrationDate]) / 365.0

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 07:15:29

Thanks. But the qryStudentSuspGroup.Suspension is a query that exists in the Access version of my application. I don't know how to incorporate it into the SQL statement for my SQL Server version. When I try to nest it, I get errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 07:16:32

Post the qryStudentSuspGroup query, and we will try to rewrite it as a view.

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 07:18:36

SELECT StudentSuspension.ResearchStudentID, DATEDIFF(Day,StartDate,EndDate) As Suspension FROM StudentSuspension

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 07:25:41

Try this

DECLARE @Input INT

SELECT @Input = 2004

SELECT		@Input AS [input],
		ResearchStudent.Department,
		ResearchStudent.DateAwarded,
		ResearchStudent.StudentNumber,
		Person.Forenames AS fore,
		Person.Surname AS Sur,
		ResearchStudent.Mode,
		ResearchStudent.RegistrationDate,
		StudentExamination.Decision,
		CASE WHEN StudentSuspension.Suspension IS NULL OR StudentSuspension.Suspension = 0 THEN ([DateAwarded] - [RegistrationDate]) / 365.0, ([DateAwarded] - [RegistrationDate] - StudentSuspension.Suspension) / 365.0 END AS CompDate,
		ResearchStudent.EnrollmentCategory,
		qryStudentSuspGroup.Suspension
FROM		ResearchStudent
LEFT JOIN	Person ON ResearchStudent.ResearchStudentID = Person.PersonID
LEFT JOIN	qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID
LEFT JOIN	StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
LEFT JOIN	(
			SELECT	StudentSuspension.ResearchStudentID,
				DATEDIFF(Day, StudentSuspension.StartDate, StudentSuspension.EndDate) As Suspension
			FROM	StudentSuspension
		) StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID
WHERE 		Year([DateAwarded]) BETWEEN @Input AND @Input + 1
		AND	CASE
				WHEN Year([DateAwarded]) = @Input AND Month([DateAwarded]) > 8 THEN 1
				WHEN Year([DateAwarded]) <> @Input AND Month([DateAwarded]) < 9 THEN 1
				ELSE 0
			END = 1
ORDER BY	ResearchStudent.Department,
		ResearchStudent.Mode,
		([DateAwarded] - [RegistrationDate]) / 365.0

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 07:42:07

I'm still getting errors, it says there is incorrect syntax near the ',' in the following line:
"CASE WHEN StudentSuspension.Suspension IS NULL OR StudentSuspension.Suspension = 0 THEN ([DateAwarded] - [RegistrationDate]) / 365.0,"

and also incorrect syntax near 'StudentSuspension' in the following line:

") StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:01:41

You can not run the suggestion above in ACCESS, it is an SQL Server query.
Also it is a very good practice to prefix your columns with table name for easier reading and debugging.

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 08:38:54

I'm running it on the SQL Server SQL Query Analyser. That's where I'm getting the errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:47:28

My mistake. Replace COMMA with keyword " ELSE ".

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 09:32:27

Sorry I'm still getting an error, it now doesn't like the part where you divide by 365, the error message is as follows:

"Invalid operator for data type. Operator equals divide, type equals smalldatetime."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 09:36:03

[code]DECLARE @Input INT

SELECT @Input = 2004

SELECT @Input AS [input],
ResearchStudent.Department,
ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber,
Person.Forenames AS fore,
Person.Surname AS Sur,
ResearchStudent.Mode,
ResearchStudent.RegistrationDate,
StudentExamination.Decision,
CASE WHEN StudentSuspension.Suspension IS NULL OR StudentSuspension.Suspension = 0 THEN DATEDIFF(day, [RegistrationDate], [DateAwarded]) / 365.0 ELSE (DATEDIFF(day, [RegistrationDate], [DateAwarded]) - StudentSuspension.Suspension) / 365.0 END AS CompDate,
ResearchStudent.EnrollmentCategory,
qryStudentSuspGroup.Suspension
FROM ResearchStudent
LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
LEFT JOIN (
SELECT StudentSuspension.ResearchStudentID,
DATEDIFF(Day, StudentSuspension.StartDate, StudentSuspension.EndDate) As Suspension
FROM StudentSuspension
) StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID
WHERE Year([DateAwarded]) BETWEEN @Input AND @Input + 1
AND CASE
WHEN Year([DateAwarded]) = @Input AND Month([DateAwarded]) > 8 THEN 1
WHEN Year([DateAwarded]) <> @Input AND Month([DateAwarded]) < 9 THEN 1
ELSE 0
END = 1
ORDER BY ResearchStudent.Department,
ResearchStudent.Mode,
DATEDIFF(day, [RegistrationDate], [DateAwarded]) / 365.0[/code]

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 09:39:52

That's working now. Thank you so much for your help Peter.

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-19 : 11:55:46

One more question!

I'm trying to set up a modified version of this code as a view on my SQL Server database. I'm getting 2 errors, one says that the "Query Designer does not support the Case SQL Construct" but I have examples of Case being used in other views and have set it up to be the same.

It then goes on to give me the second error which says that "View definition includes no output columns or includes no items in the FROM clause".

I'm baffled by both of these errors!!! The statement I am trying to put into the view is as follows:

SELECT ResearchStudent.DateAwarded, ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode, ResearchStudent.RegistrationDate, Department.Name, EnrolmentCategory.ECName, StudentExamination.Decision,
(CASE StudentSuspension.Suspension WHEN 'NULL' OR '0'
THEN DATEDIFF(Day, RegistrationDate, DateAwarded) / 365.0
ELSE (DATEDIFF(Day, RegistrationDate, DateAwarded) - StudentSuspension.Suspension) / 365.0 END) AS CompDate
FROM ResearchStudent
LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID
LEFT JOIN Department ON ResearchStudent.DeptID = Department.ID
LEFT JOIN EnrolmentCategory ON ResearchStudent.EnrolmentCategoryID = EnrolmentCategory.ID
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
LEFT JOIN
(SELECT StudentSuspension.ResearchStudentID, DATEDIFF(Day, StudentSuspension.StartDate, StudentSuspension.EndDate) AS Suspension
FROM StudentSuspension)
StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID
ORDER BY Department.Name, ResearchStudent.Mode, DATEDIFF(day, [RegistrationDate], [DateAwarded]) / 365.0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 12:18:54

The query designer does not like CASE. Run this

CREATE VIEW	dbo.vwSomeName
AS

SELECT		TOP 100 PERCENT ResearchStudent.DateAwarded,
		ResearchStudent.StudentNumber,
		Person.Forenames AS fore,
		Person.Surname AS Sur,
		ResearchStudent.Mode,
		ResearchStudent.RegistrationDate,
		Department.Name,
		EnrolmentCategory.ECName,
		StudentExamination.Decision,
		CASE
			WHEN StudentSuspension.Suspension IS NULL OR StudentSuspension.Suspension = 0 THEN DATEDIFF(day, RegistrationDate, DateAwarded) / 365.0 
			ELSE DATEDIFF(day, RegistrationDate, DateAwarded) - StudentSuspension.Suspension) / 365.0
		END AS CompDate
FROM		ResearchStudent 
LEFT JOIN	Person ON ResearchStudent.ResearchStudentID = Person.PersonID 
LEFT JOIN	Department ON ResearchStudent.DeptID = Department.ID 
LEFT JOIN	EnrolmentCategory ON ResearchStudent.EnrolmentCategoryID = EnrolmentCategory.ID 
LEFT JOIN	StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID 
LEFT JOIN	(
			SELECT	StudentSuspension.ResearchStudentID,
				DATEDIFF(day, StudentSuspension.StartDate, StudentSuspension.EndDate) AS Suspension
			FROM	StudentSuspension
		) StudentSuspension ON ResearchStudent.ResearchStudentID = StudentSuspension.ResearchStudentID
ORDER BY	Department.Name,
		ResearchStudent.Mode,
		DATEDIFF(day, [RegistrationDate], [DateAwarded]) / 365.0

And the use of ORDER BY in a view is reduntant. You should user ORDER BY first when selecting data from the view as

SELECT * FROM dbo.vwSomeName ORDER BY Name, Mode, CompDate

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-20 : 06:26:31

Thank you! that's working fine now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources