Using User-Defined Column name in where clause

[MichaelaLee]

Hi Everyone,
I'm using SQL Server 2000 and have a slight problem. In my query I use a User-Defined function to get the column value as follows:
SELECT UserID, Max(Termination.DOA) AS LastAdmission,
PATIENTS.LastName + ', ' + PATIENTS.FirstName as Name,
DateAdd(m, 3, Max(Termination.DOA)) as FirstProgressDate,
dbo.nf_ComputeDate(GetDate(),Max(Termination.DOA)) AS NextDue,
DateAdd("m",-3,dbo.nf_ComputeDate(GetDate(),DateAdd(m, 3,Max(Termination.DOA)) )) AS CurrentDue
FROM Termination INNER JOIN PATIENTS ON Termination.PatientID = PATIENTS.PatientID
WHERE Termination.DOT Is Null and (NextDue >= '11/1/2004' and NextDue <= '12/30/2004')
GROUP BY Termination.PatientID, PATIENTS.LastName, PATIENTS.FirstName

As you can tell from the query above, I have three defined column names "FirstProgressDate", "NextDue" and "CurrentDue". In the where clause I need to test if the NextDue is within a date range. But if I try to run the query the way it stands I get an error:
Invalid column name 'NextDue'
Do you all know of a good way to handle this one. Thanks for any help.
Michael

 

[gradley]

Hi Michael,

To the best of my knowledge you have to use the same function in the Where clause because it doesn't recognize the alias names you have defined.

 

[ESquared]

You either have to repeat the whole expression or encapsulate the whole query inside parentheses as a table, and refer to the column name from outside.

SELECT
	UserID,
	LastAdmission = Max(Termination.DOA),
	[Name] =        P.LastName + ', ' +  P.FirstName,
	FirstProgressDate = DateAdd(m, 3, Max(Termination.DOA)), 
	NextDue =       dbo.nf_ComputeDate(GetDate(),Max(Termination.DOA)),
	CurrentDue =    DateAdd("m",-3,dbo.nf_ComputeDate(GetDate(),DateAdd(m, 3,Max(Termination.DOA))))
FROM
	Termination
	INNER JOIN PATIENTS P ON Termination.PatientID = P.PatientID
WHERE
	Termination.DOT Is Null
	AND dbo.nf_ComputeDate(GetDate(),Max(Termination.DOA)) >= '11/1/2004'
	AND dbo.nf_ComputeDate(GetDate(),Max(Termination.DOA)) <= '12/30/2004'
GROUP BY
	Termination.PatientID,
	P.LastName,
	P.FirstName

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[MichaelaLee]

Hi Guys,
First off I want to thank you all for the replys. It has been helpful.
Now I have another question. I have been doing some testing with a table I have. The table contains three fields that are calculated fields and I'm having a slight problem with them at this time. I have set the 3 fields as calculated by adding a function to the table to calculate the field value. What can the side affects of using calculated fields.
I ask that question because of the following:
1. If I try and run a stored procedure to get all the fields, for some reason ADO does not see the fields. I was thinking that maybe because of the CursorType and LockType properties. So I set them for read only and Static. But still ADO don't see the fields. What Happened?
2. This may not be related. but when I add a record to that table (I'm not setting the calculated fields), I'm getting the following error:
"Syntax error converting the varchar value 'User.Name' to a column of data type int."
The code is easy anough:
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_AddQuarterly"
mCommand.ActiveConnection = ado
mCommand.Parameters.Refresh
mCommand.Parameters("@PatientId").Value = lPatientId
mCommand.Parameters("@UserId").Value = UserID
mCommand.Parameters("@CounselorId").Value = lCounselorId
mCommand.Parameters("@CounselorName").Value = lCounselorName & ""
mCommand.Parameters("@Name").Value = lName & ""
mCommand.Parameters("@AdmissionDate").Value = lAdmissionDate
mCommand.Execute

The Error seams to relate to the "@UserId" field which is a varchar in the table and the stored procedure. I've checked all the fields and they all seem to be correct. What to you think it could be. Thanks for any help.
Michael Lee

 

[SQLSister]

First, let's see if your problem is with the user interface or the sp. Does the sp run properly when you run it in query Anlayzer?

Also if you are running this code from Access, did you refresh the linked table in Access after you added the calculated fields?

Why are you using a cursor? Almost all cursor use is unnecessary and it has a very bad impact on performance.


Questions about posting. See faq183-874