Column Name Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Column Name Errors

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-22 : 13:05:56

Good afternoon, I have a pretty simple query but cannot seem to get it to work. It errors on the following:

Invalid column name 'LastName'.
Invalid column name 'FirstName'.

Here is the query, can someone help

Thanks

use ncos
go
select distinct nc_displayname as DisplayName
,nc_email as Email
,LEFT(nc_displayname,CHARINDEX(' ',nc_displayname + ' ')-1) AS FirstName
,CASE WHEN LEN(nc_displayname) - LEN(REPLACE(nc_displayname,' ','')) > 0
THEN PARSENAME(REPLACE(nc_displayname,' ','.'),1)
ELSE
NULL
END AS LastName
,LastName + ',' + FirstName as SearchName

from ncos_domainuser ORDER BY NC_DisplayName

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 13:39:50

An alias you define in the select clause (in this case Firstname and LastName) are not available for use in other columns in the select clause in the same query. So either you have to repeat your expressions (see 1 below), or make the query into a subquery (see 2 below) Also, if there is a possibility that firstname or lastname can be null, you should account for that (see the second example)

-------------- 1
SELECT DISTINCT
   nc_displayname AS DisplayName ,
   nc_email AS Email ,
   LEFT(nc_displayname, CHARINDEX(' ', nc_displayname + ' ') - 1) AS FirstName ,
   CASE WHEN LEN(nc_displayname) - LEN(REPLACE(nc_displayname, ' ', '')) > 0
        THEN PARSENAME(REPLACE(nc_displayname, ' ', '.'), 1)
        ELSE NULL
   END AS LastName ,
   LEFT(nc_displayname, CHARINDEX(' ', nc_displayname + ' ') - 1) 
   + ',' + 
   CASE WHEN LEN(nc_displayname) - LEN(REPLACE(nc_displayname, ' ', '')) > 0
        THEN PARSENAME(REPLACE(nc_displayname, ' ', '.'), 1)
        ELSE NULL
   END AS SearchName
FROM
   ncos_domainuser
ORDER BY
   NC_DisplayName 
   
---------- 2
USE ncos
go
SELECT * ,
	   COALESCE(LastName ,'') + COALESCE(',' + FirstName,'') AS SearchName
FROM 
(
	SELECT DISTINCT
	   nc_displayname AS DisplayName ,
	   nc_email AS Email ,
	   LEFT(nc_displayname, CHARINDEX(' ', nc_displayname + ' ') - 1) AS FirstName ,
	   CASE WHEN LEN(nc_displayname) - LEN(REPLACE(nc_displayname, ' ', '')) > 0
			THEN PARSENAME(REPLACE(nc_displayname, ' ', '.'), 1)
			ELSE NULL
	   END AS LastName
	FROM
	   ncos_domainuser
) s	 
ORDER BY
   NC_DisplayName

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-22 : 13:54:51

That was quick and perfect, Thank You

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 15:18:34

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources