Weird Formatting in a view

[RayMilhon]

I have a SQL Server Table with a Varchar(10) Field. I've created a view to link to another table that has the same data (almost). The 1st table has Diagnosis Code Data such as 250.5 The second table from another source has Diagnosis Code data 2505. So I've created a view that has both formats so the 2 tables can be linked with the Replace(diags,'.','') as convdiag. Then in my query I link from table 1 to the intermediate view on diags and link to the 2nd table on convdiag to diagnosiscode. However I'm getting an error and when looking at the view the convdiag is formatted as varchar(8000) not (10) How do I fix that? I tried putting the ltrim and rtrim around the replace but apparently it still sets it up as a varchar(8000)????

[pbaldy]

Shot in the dark, but what about wrapping the Replace() in a CAST() function?

[RayMilhon]

Actually found out I don't need to. the problem existed because I used that view as a linked table in Access and the link was failing due to the data type difference. But since all of the required tables for the query were in sql Server (different databases but still all SQL Server I just moved the query to SQL Server and made it a view. Problem solved the link works within SQL Server and I'm getting the data expected. Still validating just to be sure but so far I'm 20 out of 20.

[pbaldy]

Glad you got it sorted out.

[Dal Jeanis]

Another alternative would have been to wrap the REPLACE() with a LEFT(,10) to force down the size.