LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
92

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

How do I assign an alias to a case statement?

Microsoft SQL Server
Please wait...

How do I assign an alias to a case statement?

Postby Remou on Sat Dec 07, 2013 4:21 pm

I have been fiddling for too long. I am trying to create something like the procedure below, but I keep getting an error Invalid column name 'newcol' when I say 'Soundex(NewCol)', but if I leave it as 'Soundex(Surname)', then I do not get the results I would expect.

The problem is with returning a suitable set of names when names contain a space or apostrophe, as Irish names often do. The query part seems to return what I want, but the WHERE statement is a mess.

How many stupid mistakes am I making, and how can I fix them, please?

  1. CREATE PROCEDURE [SurnameSoundex]
  2.     @SurnameCompare nvarchar(30)   
  3. AS
  4.  
  5. SET NOCOUNT ON
  6.  
  7. SELECT
  8.         Forename
  9.     , (CASE
  10.         WHEN Surname Like 'O''%' THEN
  11.         Replace(Surname,'''','')
  12.         WHEN Surname Like '% %' THEN
  13.         Replace(Surname,' ','')
  14.         Else
  15.         Surname
  16.         END ) NewCol
  17. FROM [dbo].[Persons]
  18.  
  19. WHERE
  20.     Soundex(NewCol)=Soundex(@SurnameCompare)
  21. GO
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: How do I assign an alias to a case statement?

Postby SQLDenis on Sat Dec 07, 2013 8:16 pm

A query executes in this order

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

As you can see WHERE executes before SELECT and thus NewCol can't be found by the WHERE

what you can do is this

  1. SELECT * FROM(
  2. SELECT
  3.         Forename
  4.     , (CASE
  5.         WHEN Surname Like 'O''%' THEN
  6.         Replace(Surname,'''','')
  7.         WHEN Surname Like '% %' THEN
  8.         Replace(Surname,' ','')
  9.         Else
  10.         Surname
  11.         END ) NewCol
  12. FROM [dbo].[Persons] ) X
  13. WHERE
  14.     Soundex(NewCol)=Soundex(@SurnameCompare)



Just be aware that since you are using a function against the column you will get a SCAN
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: How do I assign an alias to a case statement?

Postby Remou on Sat Dec 07, 2013 9:41 pm

Thank you.

So, given that soundex asks for <first letter> followed by the code, would I be better off creating a sub query (sub table?) that asks for all data matching first letter before asking for a soundex?
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated