[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Boolean field

Man Utd

3/27/2007 7:28:00 AM

I am an application programmer using a front end component which has a
property link to a Boolean field of a dataset.
It is fine when I connect the MS Access, as MS Access has field type Yes/No.

However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
How do I return the field as boolean by:
SELECT if field1 value is 1 then true else false end
something like that ?


8 Answers

xyb

3/27/2007 7:36:00 AM

0

On 3?27?, ??3?28?, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
> I am an application programmer using a front end component which has a
> property link to a Boolean field of a dataset.
> It is fine when I connect the MS Access, as MS Access has field type Yes/No.
>
> However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
> How do I return the field as boolean by:
> SELECT if field1 value is 1 then true else false end
> something like that ?

use CASE clause.
select case field1 when 1 then True else False end as booleancolumname
from tablename...
:)

Tibor Karaszi

3/27/2007 10:12:00 AM

0

You can't return a Boolean in the column list of a SELECT statement, since there is no Boolean
datatype in SQL Server. You can for instance CAST to BIT and use CASE (see xyb's suggestion), but
the front-end tool will do the interpretation whether 1 is true or false.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
news:u2wm9FEcHHA.5052@TK2MSFTNGP06.phx.gbl...
>I am an application programmer using a front end component which has a property link to a Boolean
>field of a dataset.
> It is fine when I connect the MS Access, as MS Access has field type Yes/No.
>
> However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
> How do I return the field as boolean by:
> SELECT if field1 value is 1 then true else false end
> something like that ?
>


Man Utd

3/27/2007 12:33:00 PM

0

I have already a field of type integer of value 1 or 0.
As you suggest that it seems I don't need to CAST, I just return the field
value?

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:uETCVhFcHHA.4888@TK2MSFTNGP06.phx.gbl...
> You can't return a Boolean in the column list of a SELECT statement, since
> there is no Boolean datatype in SQL Server. You can for instance CAST to
> BIT and use CASE (see xyb's suggestion), but the front-end tool will do
> the interpretation whether 1 is true or false.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
> news:u2wm9FEcHHA.5052@TK2MSFTNGP06.phx.gbl...
>>I am an application programmer using a front end component which has a
>>property link to a Boolean field of a dataset.
>> It is fine when I connect the MS Access, as MS Access has field type
>> Yes/No.
>>
>> However, for SQL 2000 it has no data type of Boolean or Logical or
>> Yes/No.
>> How do I return the field as boolean by:
>> SELECT if field1 value is 1 then true else false end
>> something like that ?
>>
>
>
>


Looch

3/27/2007 2:49:00 PM

0

On Mar 27, 3:28 am, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
> I am an application programmer using a front end component which has a
> property link to a Boolean field of a dataset.
> It is fine when I connect the MS Access, as MS Access has field type Yes/No.
>
> However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
> How do I return the field as boolean by:
> SELECT if field1 value is 1 then true else false end
> something like that ?

VB.NET 2005 has a ToString() function that I used in a similar
situation which converted the value (Output param from select
statement) to either "True" or "False" and adjusted the code
accordingly.

Tibor Karaszi

3/27/2007 3:27:00 PM

0

> As you suggest that it seems I don't need to CAST, I just return the field value?

It depends on the client app/tool. Some app/tools will see a bit datatype and interpret it as a
Boolean, while the same values as int might not be interpreted as Booleans.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
news:ez0XFvGcHHA.1148@TK2MSFTNGP02.phx.gbl...
>I have already a field of type integer of value 1 or 0.
> As you suggest that it seems I don't need to CAST, I just return the field value?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:uETCVhFcHHA.4888@TK2MSFTNGP06.phx.gbl...
>> You can't return a Boolean in the column list of a SELECT statement, since there is no Boolean
>> datatype in SQL Server. You can for instance CAST to BIT and use CASE (see xyb's suggestion), but
>> the front-end tool will do the interpretation whether 1 is true or false.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
>> news:u2wm9FEcHHA.5052@TK2MSFTNGP06.phx.gbl...
>>>I am an application programmer using a front end component which has a property link to a Boolean
>>>field of a dataset.
>>> It is fine when I connect the MS Access, as MS Access has field type Yes/No.
>>>
>>> However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
>>> How do I return the field as boolean by:
>>> SELECT if field1 value is 1 then true else false end
>>> something like that ?
>>>
>>
>>
>>
>
>


Man Utd

3/28/2007 12:01:00 AM

0

As my situation is a bit complicated:
My application needs to support 2 databases: MS Access and SQL Server.

So I need to use the same database structure in both databases.
If the field I defined as number (ie. integer) in MS Access and SQL Server,
then I need to CAST the field to BIT in SQL statement.

How do I do that to make compatible to MS Access and SQL Server syntax?

"Looch" <lucianoj2005@yahoo.com> wrote in message
news:1175006939.931095.162880@p15g2000hsd.googlegroups.com...
> On Mar 27, 3:28 am, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
>> I am an application programmer using a front end component which has a
>> property link to a Boolean field of a dataset.
>> It is fine when I connect the MS Access, as MS Access has field type
>> Yes/No.
>>
>> However, for SQL 2000 it has no data type of Boolean or Logical or
>> Yes/No.
>> How do I return the field as boolean by:
>> SELECT if field1 value is 1 then true else false end
>> something like that ?
>
> VB.NET 2005 has a ToString() function that I used in a similar
> situation which converted the value (Output param from select
> statement) to either "True" or "False" and adjusted the code
> accordingly.
>


Tibor Karaszi

3/28/2007 6:32:00 AM

0

Why not just return an integer (which I assume Access supports) and then deal with it in the app?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
news:%23kY$fwMcHHA.2120@TK2MSFTNGP03.phx.gbl...
> As my situation is a bit complicated:
> My application needs to support 2 databases: MS Access and SQL Server.
>
> So I need to use the same database structure in both databases.
> If the field I defined as number (ie. integer) in MS Access and SQL Server, then I need to CAST
> the field to BIT in SQL statement.
>
> How do I do that to make compatible to MS Access and SQL Server syntax?
>
> "Looch" <lucianoj2005@yahoo.com> wrote in message
> news:1175006939.931095.162880@p15g2000hsd.googlegroups.com...
>> On Mar 27, 3:28 am, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
>>> I am an application programmer using a front end component which has a
>>> property link to a Boolean field of a dataset.
>>> It is fine when I connect the MS Access, as MS Access has field type Yes/No.
>>>
>>> However, for SQL 2000 it has no data type of Boolean or Logical or Yes/No.
>>> How do I return the field as boolean by:
>>> SELECT if field1 value is 1 then true else false end
>>> something like that ?
>>
>> VB.NET 2005 has a ToString() function that I used in a similar
>> situation which converted the value (Output param from select
>> statement) to either "True" or "False" and adjusted the code
>> accordingly.
>>
>
>


Man Utd

3/29/2007 1:38:00 AM

0

Yes, I tried to use integer in both databases.
However, that GUI component needs a field of type boolean,
otherwise, it raises an exception.

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:u2FJ6KQcHHA.1240@TK2MSFTNGP04.phx.gbl...
> Why not just return an integer (which I assume Access supports) and then
> deal with it in the app?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
> news:%23kY$fwMcHHA.2120@TK2MSFTNGP03.phx.gbl...
>> As my situation is a bit complicated:
>> My application needs to support 2 databases: MS Access and SQL Server.
>>
>> So I need to use the same database structure in both databases.
>> If the field I defined as number (ie. integer) in MS Access and SQL
>> Server, then I need to CAST the field to BIT in SQL statement.
>>
>> How do I do that to make compatible to MS Access and SQL Server syntax?
>>
>> "Looch" <lucianoj2005@yahoo.com> wrote in message
>> news:1175006939.931095.162880@p15g2000hsd.googlegroups.com...
>>> On Mar 27, 3:28 am, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
>>>> I am an application programmer using a front end component which has a
>>>> property link to a Boolean field of a dataset.
>>>> It is fine when I connect the MS Access, as MS Access has field type
>>>> Yes/No.
>>>>
>>>> However, for SQL 2000 it has no data type of Boolean or Logical or
>>>> Yes/No.
>>>> How do I return the field as boolean by:
>>>> SELECT if field1 value is 1 then true else false end
>>>> something like that ?
>>>
>>> VB.NET 2005 has a ToString() function that I used in a similar
>>> situation which converted the value (Output param from select
>>> statement) to either "True" or "False" and adjusted the code
>>> accordingly.
>>>
>>
>>
>
>