kacpr kacpr - 3 months ago 13
SQL Question

TSQL error: Operand data type bit is invalid for add operator

I am in the process of converting some Access queries into tsql and this error pops up when I try to execute the query.
I am guessing that SQL Server does not allow adding of 'bit' types, so I found the cultprit lines where that occurs and they are:

SELECT DISTINCT
[modules].[b]+[modules].[w]+[modules].[e]+[modules].[j]+
[modules].[p]+[modules].[s] AS approvalRating, -- other columns here --


And:

ORDER BY [modules].[b]+[modules].[w]+[modules].[e]+[modules].[j]+
[modules].[p]+[modules].[s],
modulePriority.configPriority,
[modulePositionalData].[highPos]-[modulePositionalData].[lowPos]+1,
modulePositionalData.iMax;


Now I did not create the original Access queries and I have no idea what was the intention of adding that Boolean fields which makes it a bit problematic, but is there a way to allow the operations to be performed and result to be the same as performed by Access?

Answer

you can't add bit fields in sql server

But you can cast them to int

so

CAST(yourBitField as int) + CAST (yourSecondBitField as int)

By the way (would say it's due to type preference order), it will be ok if you cast only one of them

CAST(yourBitField as int) + yourSecondBitField

But casting all is probably "easier to read and understand".

Comments