• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Select Case concatenation

How can I concatenate two fields in select and one of the two in Case statement ?

See the following -

select title, "Contract Status" =
    case
        when contract = 1 then "Contract"
        when contract = 0 then "No Contract"
    end,
    hireDate
from titles

Change the above one to  -

select title + "Contract Status" =
    case
        when contract = 1 then "Contract"
        when contract = 0 then "No Contract"
    end,
    hireDate
from titles

I'll got "Incorrect syntax near '=' if I use the above at statement.

How can concatenate the case field? I'm using SQL Server 2005.

THanks for any help.
0
minglelinch
Asked:
minglelinch
  • 2
  • 2
1 Solution
 
Simone BCommented:
You don't want to use the alias when concatenating. Try this:

select title +
    case
        when contract = 1 then "Contract"
        when contract = 0 then "No Contract"
    end,
    hireDate
from titles
0
 
Anuradha GoliCommented:
select title +
    case
         when contract = 1 then 'Contract'
          when contract = 2 then 'No Contract'
    end as 'Contract Status',
    hireDate
from titles

Open in new window


Hope this works
0
 
minglelinchAuthor Commented:
Thanks a lot. It works.. I forgot to mention one more thing, I need to add something like else case.
something like -

select title +
    case
         when contract = 1 then 'Contract'
          when contract = 2 then 'No Contract'
          When ANY OTHER VALUE then ''    (How to this logic)
    end as 'Contract Status',
    hireDate
from titles

How add the logic? Thanks.
0
 
Simone BCommented:
select title +
    case
         when contract = 1 then 'Contract'
          when contract = 2 then 'No Contract'
          else ''    
    end as 'Contract Status',
    hireDate
from titles
0
 
minglelinchAuthor Commented:
Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now