using an alias in a where statement as a parameter

  • Thread starter Thread starter http://www.visual-basic-data-mining.net/forum
  • Start date Start date
H

http://www.visual-basic-data-mining.net/forum

Hello,

I have the following tables and have setup the following sql statement as
part of my data adapter. The problem is that I need to do a statement that
will find the records selected in my list box. I cannot figure out how to
use the "name' alias in the parameter statement.

I am trying to combine first, last name, alpha number into one field for the
list box and all the requests the employee made.

Here are my two tables:

EmpAlpha RequestID
EmpFirstName RequestDate
EmpLastName DivsionID

Here is my SQL statement as it is now. But it does not return the matching
requests.

SELECT EmployeeCurrent.EmpLastName + ', ' + ' ' +
EmployeeCurrent.EmpFirstName + ' 'EmployeeCurrent.EmpAlpha AS Name,
RequestDesired.RequestID, RequestDesired.RequestDate,
RequestDesired.DivisionID, RequestDesired.CodeID
FROM EmployeeCurrent INNER JOIN
RequestDesired ON EmployeeCurrent.EmpAlpha =
RequestDesired.EmployeeAlpha
WHERE ('Name' = ? + '%')
ORDER BY RequestDesired.DivisionID

How do I reference the alias for my concatenated 'Name' in the parameter so
I can return the right records in the data adapter and set the datasource
for my grid to include 'Name'

Debbie
 
something like

SELECT EmployeeID, FirstName + ' ' + LastName AS Name, Title, BirthDate,
HireDate
FROM Employees
WHERE (FirstName + ' ' + LastName LIKE 'M%')

or

SELECT EmployeeID, Name, Title, BirthDate, HireDate
FROM (SELECT EmployeeID, FirstName + ' ' + LastName AS Name,
Title, BirthDate, HireDate
FROM Employees) selEmps
WHERE (Name LIKE 'M%')

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
 
Back
Top