Create view with field combination

Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%

So, I always get a message error said wrong datatype, how can i do, please help me.A view is just a select statement basically, meaning if you can't get something to compile in Query Analyzer it's not going to compile as a view. The syntax for a select statement similar to yours (there's nothing like yours that will actually compile) is:

SELECT column1 + '' + column2 + '' + column3 AS all_fields
FROM x
WHERE column1 + '' + column2 + '' + column3 LIKE '%my_str%'

Notice the single quotes and the fact that the column alias was not used in the where clause.

It's kind of like the following SELECT statements:

select name as huh from sysobjects where huh like '%a%'
select name from sysobjects where name like '%a%'

Only one of those actually pretends to work. :)|||The alias (all_field) is applied to the result set at the end of execution, so you can't reference in your statement.

In addition to Derrick;s method, this would normally work:

select all_field
from (select a + ' ' + b + ' ' + c as all_field from x) CombinedColumns
where all_field like %my_str%

...but I think you are going to run into problems with the TEXT column type. Notice that it's size is only 16 bytes. That is because the TEXT column is actually just a pointer to the location where the actual column value is stored. So your statement is trying to concatenate a pointer address to the end of your nvarchar and smalldatetime fields. I'm sure that's not what you want.
You will need to concatenate the actual value of column C, not it's address.