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
 General SQL Server Forums
 New to SQL Server Programming
 ALTER VIEW to 9 digit get zip code with dash
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 12/27/2008 :  16:10:18  Show Profile  Reply with Quote
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

Edited by - rjackman1959 on 12/27/2008 17:25:45

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 12/27/2008 :  18:15:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 12/27/2008 :  18:29:41  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2008 :  20:20:58  Show Profile  Reply with Quote

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

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 12/28/2008 :  09:19:22  Show Profile  Reply with Quote
Thank you Jim it's perfect
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.06 seconds. Powered By: Snitz Forums 2000