ALTER VIEW to 9 digit get zip code with dash

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

General SQL Server Forums

New to SQL Server Programming

ALTER VIEW to 9 digit get zip code with dash

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2008-12-27 : 16:10:18

I am trying to create a view with employee information that will separate the 9 digit zip code into 5 + '-' + 4 and I am getting this error. I'm sure this is an easy fix, but I am new to SQL. Can anyone help me please?

Msg 156, Level 15, State 1, Procedure view_name, Line 14
Incorrect syntax near the keyword 'else'.

ALTER VIEW [dbo].[view_name]
AS
select
mpp_firstname,
mpp_lastname,
mpp_address1,
mpp_address2,
cty_name,
cty_state,
case
when (select (right(mpp_zip, 5))) = (select (left(mpp_zip, 5)))
then (select (left(mpp_zip, 5)) as zip_code

else
left(mpp_zip, 5) + ' - ' + right(mpp_zip, 4) as zip_code
end

from manpower as m left join city as c on mpp_city = cty_code
where mpp_terminationdt >= CURRENT_TIMESTAMP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-27 : 18:15:57

Do this type of formatting inside your application, not in SQL. SQL should return raw data.

But to answer your question, you need to remove "select" from the "when" and "then".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2008-12-27 : 18:29:41

Thanks, but I took the when out and now I'm getting this error.

Msg 156, Level 15, State 1, Procedure wynne_v_drivers_labels, Line 12
Incorrect syntax near the keyword 'as'.

ALTER VIEW [dbo].[view_name]
AS
select
mpp_firstname,
mpp_lastname,
mpp_address1,
mpp_address2,
cty_name,
cty_state,
case
when right(mpp_zip, 5) = left(mpp_zip, 5)
then left(mpp_zip, 5 ) as zip_code
else
left(mpp_zip, 5) + ' - ' + right(mpp_zip, 4) as zip_code
end
from manpower as m left join city as c on mpp_city = cty_code
where mpp_terminationdt >= CURRENT_TIMESTAMP

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-27 : 20:20:58

ALTER VIEW [dbo].[view_name]
AS
select
mpp_firstname,
mpp_lastname,
mpp_address1,
mpp_address2,
cty_name,
cty_state,
case
when right(mpp_zip, 5) = left(mpp_zip, 5)
then left(mpp_zip, 5 ) ~~as zip_code~~
else
left(mpp_zip, 5) + ' - ' + right(mpp_zip, 4) as zip_code
end as zip_code
from manpower as m left join city as c on mpp_city = cty_code
where mpp_terminationdt >= CURRENT_TIMESTAMP

JIM

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2008-12-28 : 09:19:22

Thank you Jim it's perfect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources