boolean value in case statement in sql query

I am using case statement in select query some thing like this.

Select col1, col2, isActive = case when col3 = 'abc' then 1  else  0 end, col4
from <tablename> where <some condition>.

when i read the 'isActive' column value in ado.net -

bool isActiveFlag = (bool)datareader["isActive"];

I get type cast error. because it is returning value as int.

If i change query to

Select col1, col2, isActive = case when col3 = 'abc' then 'true' else 'false' end, col4
from <tablename> where <some condition>.

I get same type cast error because this time returned value is string. Basically i want query to assign boolean value to 'isActive' in query, so that i can read it as boolean value.

bool开发者_Go百科 isActiveFlag = (bool)datareader["isActive"];

Any idea how to deal with this ?

I don't want to read the value as int or string and then convert the value to boolean.

You could do:

bool isActiveFlag = (int)datareader["isActive"] != 0;

You didn't mention which dbms you are using, but if SQL Server you could do something like:

Select col1, col2, isActive = CONVERT(bit, case when col3 = 'abc' then 1  else  0 end), col4  from <tablename> where <some condition>

which should return your value as a bool making

bool isActiveFlag = (bool)datareader["isActive"];

work as expected.

If I'm not mistaken this is because SQL server per-se does not have a boolean datatype, so there is no way to specify a boolean literal in a query. .NET maps the bit datatype to bool, but there is also no way to specify a bit-literal in a query string without using CONVERT or CAST. The literal 1 is interpreted as an int value as you've already seen.

The right string values for boolean is 'True' and 'False', so use in your query:

Select col1, col2, isActive = case when col3 = 'abc' then 'True' else 'False' end, col4
from <tablename> where <some condition>

Another option :

declare @true bit = 1
declare @false bit = 0

Select col1, col2, isActive = case when col3 = 'abc' then @true else @false end, col4
from <tablename> where <some condition>

Probably overkill in this case, but maybe useful when your sql is multi-statement and you would reference the variables multiple times.