Aliasing Fieldnames and adding a calculation Field

Raabi Anony

Hello everybody! I am facing two simple problems in the following code:

    Dim cmdText As String
    cmdText = "SELECT tblStaffInfo.StaffName, tblCampuses.CampusName, 
               LessonPlanning, LessonPreprn, Regularity,
	           ((LessonPlanning + LessonPreprn + Regularity)/3 ) AS AvgScore
                FROM tblStaffInfo
                INNER JOIN tblCampuses
                ON tblStaffInfo.CampusID = tblCampuses.CampusID
                INNER JOIN tblStaffEvaluation
                ON tblStaffEvaluation.StaffID = tblStaffInfo.StaffID
                ORDER BY tblStaffInfo.StaffName"

(1)- The above Sql shows hatred for the division (/) sign (2)- How can use short alias for the long tables' name; like tblStaffInfo and tblCampuses etc. Please suggest!

CHill60

To answer your second question first ... You can give each table a short alias by just including that alias after the table name in the query, or by using AS aliasname. For example you can use either

FROM tblStaffInfo AS A
INNER JOIN tblCampuses AS B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation AS C ON **C.**StaffID = A.StaffID

or

FROM tblStaffInfo A
INNER JOIN tblCampuses B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation C ON **C.**StaffID = A.StaffID

Once you have given a table an alias you can then no longer use the tablename to qualify which fields you want - you must use the alias. For example, this query

SELECT A.StaffName, tblCampuses.CampusName,
LessonPlanning, LessonPreprn, Regularity,
((LessonPlanning + LessonPreprn + Regularity) / 3) AS AvgScore
FROM tblStaffInfo A
INNER JOIN tblCampuses B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation C ON C.StaffID = A.StaffID
ORDER BY A.StaffName

will generate the error

Quote:

Msg 4104, Level 16, State 1, Line 24 The multi-part identifier "tblCampuses.CampusName" could not be bound.

It should be B.CampusName. For the first part of your question you will have to explain to us what "The above Sql shows hatred for the division (/) sign" means. There is no error generated by your query and if the fields are defined as INT on the table schema then it gives the correct results. You will get strange results if you have stored those scores as char or varchar fields.

Dave Kreskowiak

(1) For the average problem, are any of these columns nullable? If so, your statement needs to be a bit more complex because NULL is not the same as 0. You might want to try this[^]. (2) As for the alias, it's ridiculously simple:

SELECT si.StaffName, ci.CampusName, ...
FROM tblStaffInfo AS si
INNER JOIN tblCampuses AS ci
ON si.CampusID = ci.CampusID
...

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

Raabi Anony

Thank you very much CHill60 and Dave for your help, especially very beautiful explanation by CHill60. Now, my problem got resolved. Wish you very good time and regards.