How can I concatenate a String with a Bit (documentTitle+Archive) SQL server 2005

Question

I get the following error when I try to concatenate Operand type clash: text in incompatible with bit Invalid operator for datatype: Operator equals Add, Type equal bit

SELECT
  F.SubmissionId, F.FormId, 
  F.DocumentTitle + F.Archive AS DocumentTitle,
  F.Keywords, F.PublishDate, F.PostedDate, F.ExpiredDate, 
  F.IsFlag, F.IsAdminOnly, F.IsCompleted, F.IsPublished,
  F.CreatedDate, F.AllowComments, 
  CASE WHEN F.Archive = 1 THEN 'Yes' ELSE 'No' END AS Archive, 
  I.ItemId, I.SubmissionId AS Expr1, I.ParamId, I.ParamValue
FROM
  dbo.app_FormSubmission AS F
    INNER JOIN dbo.app_FormSubmissionItems AS I ON 
      F.SubmissionId = I.SubmissionId

Answer 1

Why don't you do it in the presentation layer whatever it might be in your case?

If it's not an option, then here's what I would do. First, I'd check whether case statement works as expected and if it does, I'd use concat SQL function to concatenate strings.

And make sure that when using F.Archive in F.DocumentTitle + F.Archive you're actually referring to the result of your case clause, not to the original column.

Answer 2

you need to convert, run this to see what I mean

declare @i bit
select @i = 1

select 'abc'  + convert(varchar(1),@i) -- fine
select 'abc'  + @i  -- will fail

Answer 3

I had to do this to make it work. Thanks SQLMenace

SELECT CONVERT(varchar(50), F.DocumentTitle) + CONVERT(varchar(1), F.Archive) AS Expr1