SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Column Name Errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 04/22/2013 :  13:05:56  Show Profile  Reply with Quote
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

Edited by - bholmstrom on 04/22/2013 13:28:18

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 04/22/2013 :  13:39:50  Show Profile  Reply with Quote
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

USA
71 Posts

Posted - 04/22/2013 :  13:54:51  Show Profile  Reply with Quote
That was quick and perfect, Thank You

Bryan Holmstrom
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 04/22/2013 :  15:18:34  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000