Diether Silverious Diether Silverious - 7 months ago 26
Vb.net Question

How to filter Age from SQL SERVER

I was trying to create a filter on the

datagridview
using
combobox
.
What I wanted to do is to filter the filtered data in the
datagridview
.
I have seven
comboboxes
for the filtration. For example, the first
combobox
is for the Year then I still want to filter the search into Grade level,then to Section and so on. So, the user will be able to sort or filter his search from the database. So far i have my stored procedure code and tried it in the combo box.The SchoolYear, Grade ,section, Gender, status, Account filtering works well.. Aside from age, I set my query to compute the age automatically without saving into database, now my problem is to filter the age.

I did my stored procedure this way.

ALTER PROCEDURE [dbo].[uspYearGradeFilter]
-- Add the parameters for the stored procedure here

@Year Nvarchar(20)= NULL,
@Grade Nvarchar(20) = NULL,
@Section Nvarchar(20)= NUll,
@Gender Nvarchar(20)= NULL,
@Status Nvarchar(20)= NULL,
@Status2 Nvarchar(20)= NULL,
@Age Nvarchar(20)= NULL

AS
BEGIN

SET NOCOUNT ON;

SELECT si.StudentID,SI.Surname,SI.FirstName,SI.MiddleName,si.Gender,si.BirthDay,SI.TelNum,
Birthday,getdate() AS [Today],
Datediff(yy,BirthDay,getdate()) -
CASE
WHEN DATEADD(YY, DATEDIFF(YY,BirthDay,getdate()),BirthDay)
>GETDATE() THEN 1
ELSE 0

END AS [age]
FROM StudentInformation SI
JOIN StudentHistory SH
ON SI.StudentID = SH.StudentID


WHERE sh.SchoolYear LIKE COALESCE('%'+ @Year+'%', sh.SchoolYear)
AND sh.Levels LIKE COALESCE('%' + @Grade + '%', sh.Levels)
AND SI.Gender LIKE COALESCE('%' + @Gender + '%', si.gender)
AND SH.Section LIKE COALESCE('%' + @Section + '%', sh.Section)
AND Si.Status LIKE COALESCE('%' + @Status + '%', si.status)
AND Sh.Status2 LIKE COALESCE(@Status2 + '%', sh.status2)
AND [Age] LIKE COALESCE('%' + @Age + '%', [Age])
END


But i get an error saying that:


Msg 207, Level 16, State 1, Procedure uspYearGradeFilter, Line 42
Invalid column name 'Age'


Can someone please help me to solve this. Thanks

Answer

You cannot use the column alias in the immediate WHERE clause. You can use it in the subquery though:

SELECT * FROM
(
    SELECT 
        si.StudentID,
        SI.Surname,
        SI.FirstName,
        SI.MiddleName,
        si.Gender,
        si.BirthDay,
        SI.TelNum
        getdate() AS [Today],
        Datediff(yy,BirthDay,getdate()) -
                    CASE
                        WHEN DATEADD(YY, DATEDIFF(YY,BirthDay,getdate()),BirthDay) > GETDATE() 
                            THEN 1
                        ELSE 
                            0
                    END AS [age]
    FROM StudentInformation SI
    JOIN StudentHistory SH
        ON SI.StudentID = SH.StudentID
    WHERE 
        sh.SchoolYear LIKE COALESCE('%'+ @Year+'%', sh.SchoolYear)
        AND sh.Levels LIKE COALESCE('%' + @Grade + '%', sh.Levels)
        AND SI.Gender LIKE COALESCE('%' + @Gender + '%', si.gender)
        AND SH.Section LIKE COALESCE('%' + @Section + '%', sh.Section) 
        AND Si.Status LIKE COALESCE('%' + @Status + '%', si.status) 
        AND Sh.Status2 LIKE COALESCE(@Status2 + '%', sh.status2)
) t
WHERE [Age] LIKE COALESCE('%' + @Age + '%', [Age])