Problem with case statement and field size

[Razor1]

I am creating a view that work properly except that the field size for the item no is a varchar, 8000. The field should only be 50 characters. How do I make the ItemNo (varchar, 50)? When I link the table to Access it converts the field to a memo because of the size of the field.

Here is the case statement:
CASE WHEN InvoiceDetails.ItemNo = 'Lift Chairs' THEN RTrim(ItemSpecs.PropText) ELSE RTrim(InvoiceDetails.ItemNo) END AS ItemNo,

Thanks in advance.

Razor1

 

[gmmastros]

There are several ways. What you need to realize is that SQL Server will examine all paths of the case statement and determine the datatype (including size) based on all of the data that could possibly returned.

You could convert the data in the case statement, or you could convert the entire case statement.

CASE WHEN InvoiceDetails.ItemNo = 'Lift Chairs' 
     THEN Cast(RTrim(ItemSpecs.PropText) As VarChar(50))
     ELSE Cast(RTrim(InvoiceDetails.ItemNo) As VarChar(50))
     END AS ItemNo,

OR

Cast(CASE WHEN InvoiceDetails.ItemNo = 'Lift Chairs' 
          THEN RTrim(ItemSpecs.PropText)
          ELSE RTrim(InvoiceDetails.ItemNo)
          END As VarChar(50)) AS ItemNo,

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Razor1]

Many thanks. Have a star.

Razor1