Booleans?

Hello.
I just changed the databases of a website from Access to SQL Server.
Converting the bases went smothly, but now I recieve error message to my SQL-quereries:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

The query is: "Select pieces,date from orders where active = True AND company_id <> 19 AND authorize_wait = 0 ORDER BY send_date desc"

When I change int the query to: "active = 1" the page works like before (with Access)

I supose SQL Server is able to understand commands with True/False-quereries, so what is wrong here?
Thank you for your help
/CalleMSSQL does not define a "True" or "False" variable. You need to either use a one as you have in your example OR define a local variable

declare @.True bit
set @.True = 1
Select pieces,date
from orders
where active = @.True
AND company_id <> 19
AND authorize_wait = 0
ORDER BY send_date desc|||Thank you for your response!
/Calle

Booleans, SqlServer, and VB

Hi,

The three above seem to form an evil combination. ;-)

For now, I use varchar(5) in my SqlServer 2000 db and fill it with "True" or "False", which VB understands as boolean values in my .aspx/.aspx.vb pages.

Is there no way of using Bit instead in SqlServer, or even 0/1, and easily bind it in VB? How do others do it? (I've only seen other people struggling with this, and not any solution).

I can't understand why a language such as VB cannot be allowed to understand both True/False, 1/0, and Y/N!

Muchos gracias in advance,

Pettrer

I do not know what about Y/N but 0/1 in VB is a little opposite because is 0/-1 so -1 is true in VB and 1 is true in SQL Bit field.

Thanks

|||

Thanks, but the problem is that VB only writes "True" and "False" to the database. Of course i might be able to change it in the INSERT/UPDATE statements, but then I read somewhere that booleans are not indexed in SqlServer, so I guess that it's bo point using it anyway. Am i wrong in this assumption? Please help.

Pettrer, very puzzled ;-)

|||

If your field is bit field is stored on SQL as 0 or 1 but in gridView in VB is be displayed as True/False by default (or as check box if you ask for this )check this code :

select

cast(-10asbit) true,cast(0asbit) false,cast(1asbit) true,cast(10asbit) true

As you could see from this example I was a little wrong in SQL looks like false is 0 and everything else is true

Thanks

|||

Yes, you're right - everything that is not 0 is true; absint - not absinth! ;-) - comes to mind...

However, my questions are (forgive me if I misunderstood your reply):

1. How do I get from the checkbox to boolean in the db (the other way becomes correct automatically )?

2. Is this a behaviour I want (ie having booleans in the db), as I read somewhere that booleans are not indexed - which sounds really strange to me, as that would affect server performance)? I could of course convert everything to 0/1 as integers, but it seems rather stupid to do that. What do people do? Is no one using VB with SqlServer?

Pettrer, puzzled! :-)

|||Use bit data type which is Ture/False in the database and you can set this column to required and with a default if you want.|||

Limno,

Thanks for replying. You're right of course, but the problem is not the SqlServer db per se, but how badly VB works together with it. I took a look in the db and realised that if I do this the boolean way, it's still one byte per entry, as they can't be divided into the bits. If I use the strings True and False, I get two bytes instead. It's really not a big deal, then (my advice to other VB developers must be to use the textual representation in the db). But it's really, really strange!

Pettrer

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.
There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>
|||Although there is a BIT data type that has a value 0, 1, or NULL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
>
|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...
|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>
|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.
|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>
|||Depends if you are a Joe Celko fan or not...
http://groups.google.com/groups?as_q...ver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> at
to[vbcol=seagreen]
> worse,
The[vbcol=seagreen]
> if
BIT
>
|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
http://msdn.microsoft.com/library/de...ba-bz_2it0.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>|||Although there is a BIT data type that has a value 0, 1, or NULL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
>|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>|||Depends if you are a Joe Celko fan or not...
qlserver*&lr=&hl=en" target="_blank">http://groups.google.com/groups?as_...rver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> at
to[vbcol=seagreen]
> worse,
The[vbcol=seagreen]
> if
BIT[vbcol=seagreen]
>|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
2it0.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
2it0.asp
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to replysql

Boolean Values 0,1?

Could someone please confirm that in SQL 2000 0,1 are used to refer to
Boolean values as oppose to 0,-1 in Access.
Thank you.There is no boolean datatype in SQL Server.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> Could someone please confirm that in SQL 2000 0,1 are used to refer to
> Boolean values as oppose to 0,-1 in Access.
> Thank you.
>|||Although there is a BIT data type that has a value 0, 1, or NULL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23GFMAc9UEHA.2840@.TK2MSFTNGP11.phx.gbl...
> There is no boolean datatype in SQL Server.
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:ecXFRV9UEHA.1164@.tk2msftngp13.phx.gbl...
> > Could someone please confirm that in SQL 2000 0,1 are used to refer to
> > Boolean values as oppose to 0,-1 in Access.
> >
> > Thank you.
> >
> >
>|||"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> Although there is a BIT data type that has a value 0, 1, or NULL.
True, but that's not a boolean (although it would be closer to a boolean
if you set the column to NOT NULL); I would expect, were there a boolean
datatype, the ability to do this:
SELECT (ColA = ColB) AS ColAEqualsColB
FROM MyTable
As well as other things like a UDF returning TRUE/FALSE (rather than having
to evaluate whether it returns 1 or 0), etc...|||Thank you having 0/1 answers my q. :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23WdVqx9UEHA.484@.TK2MSFTNGP10.phx.gbl...
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ORxEyf9UEHA.2716@.tk2msftngp13.phx.gbl...
> > Although there is a BIT data type that has a value 0, 1, or NULL.
>
> True, but that's not a boolean (although it would be closer to a
boolean
> if you set the column to NOT NULL); I would expect, were there a boolean
> datatype, the ability to do this:
> SELECT (ColA = ColB) AS ColAEqualsColB
> FROM MyTable
> As well as other things like a UDF returning TRUE/FALSE (rather than
having
> to evaluate whether it returns 1 or 0), etc...
>|||"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> Thank you having 0/1 answers my q. :-)
For what it's worth, most shops I've seen don't use the BIT datatype at
all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
enforce one or the other). I'm not sure if I believe it's better or worse,
but a lot of the 'experts' seem to think it is better than using BIT. The
upside is that you can index the CHAR(1), if it's highly selective (e.g. if
a very low percentage of the rows happen to be either true or false). BIT
cannot be indexed.|||Strange as it might, most DBs I have come across used bit type for their
Boolean fields. I wonder if there are any other benefits to using Char(1)
instead of Bit (other than indexing).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> > Thank you having 0/1 answers my q. :-)
> For what it's worth, most shops I've seen don't use the BIT datatype
at
> all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint to
> enforce one or the other). I'm not sure if I believe it's better or
worse,
> but a lot of the 'experts' seem to think it is better than using BIT. The
> upside is that you can index the CHAR(1), if it's highly selective (e.g.
if
> a very low percentage of the rows happen to be either true or false). BIT
> cannot be indexed.
>|||Depends if you are a Joe Celko fan or not...
http://groups.google.com/groups?as_q=celko%20bit&safe=images&ie=UTF-8&as_ugroup=*sqlserver*&lr=&hl=en
... Or I'll save you the effort and sum it up:
"BIT is hardware dependant, not portable, and bad. And you don't and never
will understand SQL as well as I do!"
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eCLb%23qGVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> >
> > "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> > news:u%23B4U2%23UEHA.2716@.tk2msftngp13.phx.gbl...
> > > Thank you having 0/1 answers my q. :-)
> >
> > For what it's worth, most shops I've seen don't use the BIT datatype
> at
> > all. They instead use CHAR(1) with 'T' and 'F' (and a CHECK constraint
to
> > enforce one or the other). I'm not sure if I believe it's better or
> worse,
> > but a lot of the 'experts' seem to think it is better than using BIT.
The
> > upside is that you can index the CHAR(1), if it's highly selective (e.g.
> if
> > a very low percentage of the rows happen to be either true or false).
BIT
> > cannot be indexed.
> >
> >
>|||hi,
"Dragon" <NoSpam_Baadil@.hotmail.com> ha scritto nel messaggio
news:OvBs6CIVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Strange as it might, most DBs I have come across used bit type for their
> Boolean fields. I wonder if there are any other benefits to using Char(1)
> instead of Bit (other than indexing).
if space is an issue, more bit columns can be packed in 2 bytes...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

boolean value in column

I need to do an aggregate query against an integer data type, and want to
return three columns based on the value: 1, 2, or 3. If the field name is
"Action", I should be able to sum (action=1), (action=2), and (action=3).
First I tried the following:
SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2), SUM(SU.[Action]
= 3)
The result of each would be -1 or zero. That doesn't work, I guess because
no boolean data type exists. Using CAST or CONVERT doesn't work either for
the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I would
try IF/ELSE, but cannot get the syntax right. I've used it before but can't
remember how I did it. May have been with DB2. Should go something like:
SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] = 3
BEGIN 1 END ELSE BEGIN 0 END)
If someone would enlighten me I'd appreciate it. ThanksSELECT
SUM CASE WHEN foobar_action = 1 then 1 ELSE 0 END) AS total_1,
SUM CASE WHEN foobar_action = 2 then 1 ELSE 0 END) AS total_2,
SUM CASE WHEN foobar_action = 3 then 1 ELSE 0 END) AS total_3
FROM Foobar;|||David McDivitt wrote:
> I need to do an aggregate query against an integer data type, and
> want to return three columns based on the value: 1, 2, or 3. If the
> field name is "Action", I should be able to sum (action=1),
> (action=2), and (action=3). First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action] = 3)
> The result of each would be -1 or zero. That doesn't work, I guess
> because no boolean data type exists. Using CAST or CONVERT doesn't
> work either for the same reason: (SU.[Action] = 1) cannot be
> interpreted. So thought I would try IF/ELSE, but cannot get the
> syntax right. I've used it before but can't remember how I did it.
> May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF
> SU.[Action] = 3 BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
Try using a CASE statement:
create table #abc (action int)
insert into #abc values (1)
insert into #abc values (2)
insert into #abc values (2)
insert into #abc values (3)
insert into #abc values (3)
insert into #abc values (3)
Select
SUM( CASE action
WHEN 1 THEN 1
ELSE 0
END ) as "Action 1",
SUM( CASE action
WHEN 2 THEN 1
ELSE 0
END ) as "Action 2",
SUM( CASE action
WHEN 3 THEN 1
ELSE 0
END ) as "Action 3"
From #abc
Action 1 Action 2 Action 3
-- -- --
1 2 3
drop table #abc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Look up CASE WHEN in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David McDivitt" <x12code-del@.del-yahoo.com> wrote in message
news:ve9pg1dr6kvo45sc4s2mmmt003cisrhg6t@.
4ax.com...
>I need to do an aggregate query against an integer data type, and want to
> return three columns based on the value: 1, 2, or 3. If the field name is
> "Action", I should be able to sum (action=1), (action=2), and (action=3).
> First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action]
> = 3)
> The result of each would be -1 or zero. That doesn't work, I guess because
> no boolean data type exists. Using CAST or CONVERT doesn't work either for
> the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I
> would
> try IF/ELSE, but cannot get the syntax right. I've used it before but
> can't
> remember how I did it. May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] =
> 3
> BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
>|||>From: "David Gugick" <david.gugick-nospam@.quest.com>
>Date: Wed, 24 Aug 2005 13:17:27 -0400
>Lines: 62
>David McDivitt wrote:
>Try using a CASE statement:
>create table #abc (action int)
>insert into #abc values (1)
>insert into #abc values (2)
>insert into #abc values (2)
>insert into #abc values (3)
>insert into #abc values (3)
>insert into #abc values (3)
>Select
> SUM( CASE action
> WHEN 1 THEN 1
> ELSE 0
Thanks guys the CASE statement was what I was looking for.

Boolean value

How can I return boolean value to the client.
This doesn't work:
SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
If I return :
SELECT column1=case when exists (select ...) then 1 else 0 end,...
then client gets the int32 value
regards,S> How can I return boolean value to the client.
You can't. There is no Boolean datatype in TSQL. There is a bit datatype, wh
ich is a integer-class
datatype restricted to the values 0, 1 and NULL. Some libraries (ADO etc) wi
ll interpret 1 as TRUE
and 0 as FALSE. If you want to return but, you have to CAST. for example
SELECT case when exists (select ...) then CAST(1 AS bit) else CAST(0 AS bit)
end AS column1
Or you can do the CAST outside the CASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:O3fYef9oFHA.3256@.tk2msftngp13.p
hx.gbl...
> How can I return boolean value to the client.
> This doesn't work:
> SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
> If I return :
> SELECT column1=case when exists (select ...) then 1 else 0 end,...
> then client gets the int32 value
> regards,S
>|||There's no boolean data type in SQL. I'd suggest using the bit data type.
Like this:
ELECT column1=case when exists (select ...) then cast(1 as bit) else cast(0
as bit) end
ML