October 4, 2007 at 6:28 am
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where UserDataEntry.GeneralInfo.[Name] like '%abc%' ORDER BY ALName
Invalid column name.
and I tried this one too, it error out.
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where [ALName]++[AFName] as [Name] like '%abc%' ORDER BY ALName
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
The first query work in sql 2000.
I can do this
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where [ALName] like '%abc%' or [ALName] like '%abc%' ORDER BY ALName
it is cumbersome. Thx.
October 4, 2007 at 6:53 am
Frances
If you take the second "as [Name]" out of your second query, it should work.
John
October 4, 2007 at 6:57 am
Thx.
October 4, 2007 at 7:04 am
Frances L (10/4/2007)
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where UserDataEntry.GeneralInfo.[Name] like '%abc%' ORDER BY ALNameInvalid column name.
and I tried this one too, it error out.
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where [ALName]++[AFName] as [Name] like '%abc%' ORDER BY ALName
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
The first query work in sql 2000.
I can do this
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where [ALName] like '%abc%' or [ALName] like '%abc%' ORDER BY ALName
it is cumbersome. Thx.
I don't know how the first query worked in SQL Server 2000 since Name is an alias created in the query and you cannot reference aliases in Where or group by clauses.
Your second query is generating an error because you cannot create an alias using "AS" anywhere but the select list. YOur query should look like:
select [ALName] +'--' +[AFName] +'--' +[APhone] +'--' +[***] +'--' +[AAddress] as [Name] ,[***] from UserDataEntry.GeneralInfo where [ALName]+[AFName] like '%abc%' ORDER BY ALName
Notice that all I changed was the "++" to "+" and removed the "AS [Name]" from the where clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply