I want to create a view which only shows me values which have a % in it.
I tried following
Alter VIEW [dbo].[history] as
select
[objid]
,[interval]
,[value_channel]
,[value_channelid]
,case when [value_text] like '[%]' then right([value text],2)
,[value_raw_text]
,[coverage_raw]
from .....
but I get the error Incorrect syntax near ','.
any idea?
There are tow things missing in your alter view statement:
One is the end after the case, and the other one is an alias to the column.
Alter VIEW [dbo].[history] as
select
[objid]
,[interval]
,[value_channel]
,[value_channelid]
,case when [value_text] like '[%]' then right([value_text],2) end as value_text
,[value_raw_text]
,[coverage_raw]
from .....
You might also want to add an else to the case expression, otherwise it will simply return null when the condition is not matched:
Alter VIEW [dbo].[history] as
select
[objid]
,[interval]
,[value_channel]
,[value_channelid]
,case when [value_text] like '[%]' then right([value_text],2) else [value_text] end as value_text
,[value_raw_text]
,[coverage_raw]
from .....