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
Go to Top of Page

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-22 : 13:54:51
That was quick and perfect, Thank You

Bryan Holmstrom
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 15:18:34
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -