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.
| Author |
Topic |
|
jschichler
Starting Member
2 Posts |
 Posted - 2008-04-29 : 16:36:20
|
| I am new to SQL server 2005. I run a large query daily against a teradata warehouse and it populates an access database. I am now attempting to run my query in a SQL 2005 database. I get an error on this case statement:CASE WHEN CAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) = '*****' THEN SUBSTR ( CAST ( ( B.zip_cd ( format '999999999' ) ) AS char ( 9 ) ), 1, 5 ) ELSE CAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) END AS ZIP_CDThe error is: Msg 102, Level 15, State 1, Line 229Incorrect syntax near '99999'.Any help is greatly appreciated!!! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-04-29 : 17:56:32
|
FORMAT isn't a SQL function (that I know of) and I changed the CHAR(9) to a CHAR(5), but that may not be what you want:CASE WHEN CAST (B.zip_cd AS CHAR(5)) = '*****' THEN SUBSTRING(CAST(B.zip_cd AS CHAR(5)), 1, 5) ELSE CAST(B.zip_cd AS CHAR(5))END AS ZIP_CD PS - Is the data already a CHAR(5)? Just curious if you need to cast it at all...? |
 |
|
|
jschichler
Starting Member
2 Posts |
Posted - 2008-04-30 : 12:44:44
|
| Unfortunatly, the data for this field is raw user entered data...there is a lot of bad data there...some spaces, some zip5-zip4, some just zip 5, so I needed to check for all types then cast them the same.Thanks for you quick response! |
|
|
|
|
|
|
Issue with a complex case statement