CODEWITHSUNDEEP
sqlsql-servert-sql
cyberoner1
cyberoner1

Reputation: 49

MSSQL case statement

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?

Upvotes: 2

Views: 76

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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 .....

Upvotes: 5

Related Questions