USE WMS
GO

CREATE VIEW WelderVW
AS
	SELECT	E.EmployeeId, E.LastName, E.FirstName, E.Status, W.WelderId, 
			ASCII(W.WelderId) + ASCII(SUBSTRING(W.WelderId,2,1)) + ASCII(SUBSTRING(W.WelderId,3,1)) 'WeldIdAscii'
	FROM	Employee E, Welder W
	WHERE	E.EmployeeId = W.EmployeeId
		AND	E.EmployeePositionId = ( SELECT EP.EmployeePositionId
									 FROM	EmployeePosition EP
									 WHERE	LOWER(EP.PositionName) = LOWER('Welder'))