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
Thursday, March 29, 2012
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) trueAs 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 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 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 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.
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
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
Boolean to Bit?
IT seems to me that I can't write something like this:
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:
>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.
>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!
>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.
>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.
>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.
>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.
>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.
>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.
>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:
>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.
>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!
>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.
>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.
>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.
>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.
>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.
>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.
>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl
Boolean serach on MS SQL database
Anyone know how to do a bollean search on a database?
It would need to accept 'OR', 'AND' conditions.
Would it be a good idea to maybe create stored procedures and call these somehow?
Thank you in advance.
Michael O'Connorselect * from table where boolfield = 1 or boolfield = 0 and boolfield = 1
heh funny. anyways, what exactly do you mean by boolean search? Can you be more specific?sql
Boolean Report Parameters
Hi, I have created a report parameter using a Boolean data type.
When I preview my Report I can select either True or False for this parameter. Is there any way of changing the 'True' label to say something else and again the same with the 'False' label?
Click on Report-Parameters
Select your parameter and go down to Available Values
Type in a label and the value
ex.
Open - True
Closed - False
Simone
|||This brings the values up in a list box, I was hoping to keep the selection as tick boxes but amend 'True' to say 'Open' for example.|||I ran into the same issue today when I tried to change "String" parameter to "Boolean", and hoping to have radiobox instead of listbox dropdown.
No good, it'd be boolean type with dropdown, which is the same as "String" type essentially
It'd be nice to able to change label for "True" and "False"
Boolean report parameter with Yes/No choice list
SSRS 2005
In the Report Designer you can specify a Boolean report parameter with a
list of values. When the report is viewed on the Preview tab, the dropdown
list is rendered for the parameter instead of the usual True/False radio
buttons.
However, when the report is run on the Report Server, the same report
parameter is rendered with the usual True/False radio buttons and the defined
list is ignored.
Is this a bug in Report Designer, Report Server or both? Are there plans to
fix one of these so at least the behavior is consistent?
--
Chris, SSSIHello Chris,
I have reproduced this issue.
I am contacting the product team to check whether there is any fix or
workaround for this issue. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Any update on a fix or workaround for this?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I have reproduced this issue.
> I am contacting the product team to check whether there is any fix or
> workaround for this issue. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
The product team is still look into this. I will update any information
ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
After a long discussion with internal product team, this issue has a
workaround:
Use a string parameter with a Valid Values list instead.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
In the Report Designer you can specify a Boolean report parameter with a
list of values. When the report is viewed on the Preview tab, the dropdown
list is rendered for the parameter instead of the usual True/False radio
buttons.
However, when the report is run on the Report Server, the same report
parameter is rendered with the usual True/False radio buttons and the defined
list is ignored.
Is this a bug in Report Designer, Report Server or both? Are there plans to
fix one of these so at least the behavior is consistent?
--
Chris, SSSIHello Chris,
I have reproduced this issue.
I am contacting the product team to check whether there is any fix or
workaround for this issue. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Any update on a fix or workaround for this?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I have reproduced this issue.
> I am contacting the product team to check whether there is any fix or
> workaround for this issue. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
The product team is still look into this. I will update any information
ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
After a long discussion with internal product team, this issue has a
workaround:
Use a string parameter with a Valid Values list instead.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
boolean programming
Is bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).
> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530
> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).
> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530
> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?
Boolean parameters
Hello,
I'm creating a boolean parameter in Report Designer and set {Label = "Yes",
Value = True} and {Label = "No", Value = False}. Then on Preview Page I see
to radio buttons, the first one is labeled "true" and the other one is
"false". Is this a bug? Can I set the labels I want to this radio buttons?This is a known limitation and scheduled to be addressed in a future
release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>|||Changing this behavior, for Booleans, is on our wish list for inclusion in a
future release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>
I'm creating a boolean parameter in Report Designer and set {Label = "Yes",
Value = True} and {Label = "No", Value = False}. Then on Preview Page I see
to radio buttons, the first one is labeled "true" and the other one is
"false". Is this a bug? Can I set the labels I want to this radio buttons?This is a known limitation and scheduled to be addressed in a future
release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>|||Changing this behavior, for Booleans, is on our wish list for inclusion in a
future release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lesha Akulinin" <leshaakulinin@.mail.ru> wrote in message
news:%23z6nV3BZEHA.3844@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm creating a boolean parameter in Report Designer and set {Label ="Yes",
> Value = True} and {Label = "No", Value = False}. Then on Preview Page I
see
> to radio buttons, the first one is labeled "true" and the other one is
> "false". Is this a bug? Can I set the labels I want to this radio buttons?
>
Boolean parameter label
Hi all..
I have a parameter of type Boolean. The available values are non-queried
with labels 'YES' and 'NO' and the values are obviously True and False,
respectively. When the report renders, I get the two option buttons, but the
labels are 'true' and 'false'. What did I miss?
--
Michael White
Programmer/Analyst
Marion County, ORIIRC available values and labels are not applicable in case of boolean
parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <xxx.xxx.xxx> wrote in message
news:%2311IFe5ZFHA.3224@.TK2MSFTNGP10.phx.gbl...
> Hi all..
> I have a parameter of type Boolean. The available values are non-queried
> with labels 'YES' and 'NO' and the values are obviously True and False,
> respectively. When the report renders, I get the two option buttons, but
> the
> labels are 'true' and 'false'. What did I miss?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>sql
I have a parameter of type Boolean. The available values are non-queried
with labels 'YES' and 'NO' and the values are obviously True and False,
respectively. When the report renders, I get the two option buttons, but the
labels are 'true' and 'false'. What did I miss?
--
Michael White
Programmer/Analyst
Marion County, ORIIRC available values and labels are not applicable in case of boolean
parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <xxx.xxx.xxx> wrote in message
news:%2311IFe5ZFHA.3224@.TK2MSFTNGP10.phx.gbl...
> Hi all..
> I have a parameter of type Boolean. The available values are non-queried
> with labels 'YES' and 'NO' and the values are obviously True and False,
> respectively. When the report renders, I get the two option buttons, but
> the
> labels are 'true' and 'false'. What did I miss?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>sql
Boolean parameter default issue on report server
Hi Folks,
I have a report with a boolean parameter(Show Reversed Invoices). I default
it to 'No' and this works in the BIDS environment just as i want with a drop
down menu, when deployed to the reportserver it renders as True/False radio
buttons however, the False radio button is not automatically checked. What am
I missing in order to have this happen?
Thanks for your help.On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Folks,
> I have a report with a boolean parameter(Show Reversed Invoices). I default
> it to 'No' and this works in the BIDS environment just as i want with a drop
> down menu, when deployed to the reportserver it renders as True/False radio
> buttons however, the False radio button is not automatically checked. What am
> I missing in order to have this happen?
> Thanks for your help.
When you say you set the default to 'No', do you mean 'False'? If not,
you will want to change the default value to False. If so, you may
want to check the Service Pack level of SQL Server on the Report
Server, it may not be the latest and you may need to get the latest
service pack for your version of SQL Server (if SQL 2005, SP2). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for responding. I just spoke to my manager about the service pack
level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
there I'll let you know the results thereafter.
Best regards
"EMartinez" wrote:
> On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Folks,
> >
> > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > it to 'No' and this works in the BIDS environment just as i want with a drop
> > down menu, when deployed to the reportserver it renders as True/False radio
> > buttons however, the False radio button is not automatically checked. What am
> > I missing in order to have this happen?
> >
> > Thanks for your help.
>
> When you say you set the default to 'No', do you mean 'False'? If not,
> you will want to change the default value to False. If so, you may
> want to check the Service Pack level of SQL Server on the Report
> Server, it may not be the latest and you may need to get the latest
> service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Enrique,
> Thanks for responding. I just spoke to my manager about the service pack
> level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> there I'll let you know the results thereafter.
> Best regards
> "EMartinez" wrote:
> > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > Hi Folks,
> > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > down menu, when deployed to the reportserver it renders as True/False radio
> > > buttons however, the False radio button is not automatically checked. What am
> > > I missing in order to have this happen?
> > > Thanks for your help.
> > When you say you set the default to 'No', do you mean 'False'? If not,
> > you will want to change the default value to False. If so, you may
> > want to check the Service Pack level of SQL Server on the Report
> > Server, it may not be the latest and you may need to get the latest
> > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||It doesn't seem to be a Service Pack problem. I encountered the same exact
problem on SQL 2000 SP4. Oddly enough, my report had two boolean parameters,
the first of which defaulted correctly on the published Reporting Services
site, and the second did not (however, both worked fine in the Visual Studio
developer environment).
I was able to workaround the problem by renaming the parameter. At that
point, everything started working fine.
"EMartinez" wrote:
> On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Enrique,
> >
> > Thanks for responding. I just spoke to my manager about the service pack
> > level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> > there I'll let you know the results thereafter.
> >
> > Best regards
> >
> > "EMartinez" wrote:
> > > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > > Hi Folks,
> >
> > > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > > down menu, when deployed to the reportserver it renders as True/False radio
> > > > buttons however, the False radio button is not automatically checked. What am
> > > > I missing in order to have this happen?
> >
> > > > Thanks for your help.
> >
> > > When you say you set the default to 'No', do you mean 'False'? If not,
> > > you will want to change the default value to False. If so, you may
> > > want to check the Service Pack level of SQL Server on the Report
> > > Server, it may not be the latest and you may need to get the latest
> > > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > > this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> You're welcome. Let me know if I can be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
I have a report with a boolean parameter(Show Reversed Invoices). I default
it to 'No' and this works in the BIDS environment just as i want with a drop
down menu, when deployed to the reportserver it renders as True/False radio
buttons however, the False radio button is not automatically checked. What am
I missing in order to have this happen?
Thanks for your help.On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Folks,
> I have a report with a boolean parameter(Show Reversed Invoices). I default
> it to 'No' and this works in the BIDS environment just as i want with a drop
> down menu, when deployed to the reportserver it renders as True/False radio
> buttons however, the False radio button is not automatically checked. What am
> I missing in order to have this happen?
> Thanks for your help.
When you say you set the default to 'No', do you mean 'False'? If not,
you will want to change the default value to False. If so, you may
want to check the Service Pack level of SQL Server on the Report
Server, it may not be the latest and you may need to get the latest
service pack for your version of SQL Server (if SQL 2005, SP2). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for responding. I just spoke to my manager about the service pack
level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
there I'll let you know the results thereafter.
Best regards
"EMartinez" wrote:
> On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Folks,
> >
> > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > it to 'No' and this works in the BIDS environment just as i want with a drop
> > down menu, when deployed to the reportserver it renders as True/False radio
> > buttons however, the False radio button is not automatically checked. What am
> > I missing in order to have this happen?
> >
> > Thanks for your help.
>
> When you say you set the default to 'No', do you mean 'False'? If not,
> you will want to change the default value to False. If so, you may
> want to check the Service Pack level of SQL Server on the Report
> Server, it may not be the latest and you may need to get the latest
> service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Enrique,
> Thanks for responding. I just spoke to my manager about the service pack
> level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> there I'll let you know the results thereafter.
> Best regards
> "EMartinez" wrote:
> > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > Hi Folks,
> > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > down menu, when deployed to the reportserver it renders as True/False radio
> > > buttons however, the False radio button is not automatically checked. What am
> > > I missing in order to have this happen?
> > > Thanks for your help.
> > When you say you set the default to 'No', do you mean 'False'? If not,
> > you will want to change the default value to False. If so, you may
> > want to check the Service Pack level of SQL Server on the Report
> > Server, it may not be the latest and you may need to get the latest
> > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||It doesn't seem to be a Service Pack problem. I encountered the same exact
problem on SQL 2000 SP4. Oddly enough, my report had two boolean parameters,
the first of which defaulted correctly on the published Reporting Services
site, and the second did not (however, both worked fine in the Visual Studio
developer environment).
I was able to workaround the problem by renaming the parameter. At that
point, everything started working fine.
"EMartinez" wrote:
> On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Enrique,
> >
> > Thanks for responding. I just spoke to my manager about the service pack
> > level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> > there I'll let you know the results thereafter.
> >
> > Best regards
> >
> > "EMartinez" wrote:
> > > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > > Hi Folks,
> >
> > > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > > down menu, when deployed to the reportserver it renders as True/False radio
> > > > buttons however, the False radio button is not automatically checked. What am
> > > > I missing in order to have this happen?
> >
> > > > Thanks for your help.
> >
> > > When you say you set the default to 'No', do you mean 'False'? If not,
> > > you will want to change the default value to False. If so, you may
> > > want to check the Service Pack level of SQL Server on the Report
> > > Server, it may not be the latest and you may need to get the latest
> > > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > > this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> You're welcome. Let me know if I can be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Boolean Parameter
Hi,
I have a Summary report and a Detail (drillthrough) report.
the summary report displays.
Summary report
Code Snippet
Adult | Male | Count
--
Yes | Yes | 50
Yes | No | 9
No | Yes | 20
No | No | 50
| 129
When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.
The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.
Any ideas?
Hi,
This may be due to the way you pass parameters to the sub-report.
you may not get answers if you are not more specific.
How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.
This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.
Please provide some more details.
Philippe
|||Hi,
The report queries against a database field that is a boolean but can accept a NULL value.
So i configured the recieving report to accept a boolean value "True, False & also Null".
If i used LIKE as the where criteria it obviously wouldn't be as efficent.
Regards
Ian
|||What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).
One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...
The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.
>L<
|||Hi Lisa,
Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.
i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol
cheers everyone.
ian
|||>> would like a rant about the SRS report and boolean parameterI have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.
In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".
>L<
Boolean Parameter
Hi,
I have a Summary report and a Detail (drillthrough) report.
the summary report displays.
Summary report
Code Snippet
Adult | Male | Count
--
Yes | Yes | 50
Yes | No | 9
No | Yes | 20
No | No | 50
| 129
When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.
The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.
Any ideas?
Hi,
This may be due to the way you pass parameters to the sub-report.
you may not get answers if you are not more specific.
How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.
This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.
Please provide some more details.
Philippe
|||Hi,
The report queries against a database field that is a boolean but can accept a NULL value.
So i configured the recieving report to accept a boolean value "True, False & also Null".
If i used LIKE as the where criteria it obviously wouldn't be as efficent.
Regards
Ian
|||What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).
One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...
The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.
>L<
|||Hi Lisa,
Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.
i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol
cheers everyone.
ian
|||>> would like a rant about the SRS report and boolean parameterI have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.
In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".
>L<
Boolean Parameter
Hi,
I have a Summary report and a Detail (drillthrough) report.
the summary report displays.
Summary report
Code Snippet
Adult | Male | Count
--
Yes | Yes | 50
Yes | No | 9
No | Yes | 20
No | No | 50
| 129
When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.
The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.
Any ideas?
Hi,
This may be due to the way you pass parameters to the sub-report.
you may not get answers if you are not more specific.
How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.
This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.
Please provide some more details.
Philippe
|||Hi,
The report queries against a database field that is a boolean but can accept a NULL value.
So i configured the recieving report to accept a boolean value "True, False & also Null".
If i used LIKE as the where criteria it obviously wouldn't be as efficent.
Regards
Ian
|||What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).
One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...
The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.
>L<
|||Hi Lisa,
Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.
i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol
cheers everyone.
ian
|||>> would like a rant about the SRS report and boolean parameterI have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.
In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".
>L<
Boolean logic on binary data
Hello,
I have a 21-byte binary field in a SQL Server table with which I want to do boolean AND logic in a report. (Basically, I want to test whether or not individual bits are turned on.) How can I do this? Is it possible to deal with the binary data without first converting it to a string?
Thanks,
MarkYou can analyze your bitmap values using RDL expressions which are regular VB.NET.|||How exactly do I refer to the data, however? What I mean is, I need to access the bits of this big blob of binary data, but SSRS is not binary-friendly--it wants a string or an int.
All I want to do is access 1 particular bit of this binary blob. If I have to convert this to a string somewhere I will, but I'd prefer not to if at all possible.
(FYI, the report needs to analyze the logonHours attribute for an Active Directory user. This is stored as a byte array, with each bit representing 1 hour in a given week--it's 1 if the user can logon, or 0 if he cannot. I'm converting this to be a 21-byte binary field in SQL.)sql
Boolean in sqlserver 2000 and sqlserver 2005
Hi ,
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
--
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
--
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
Boolean in sqlserver 2000 and sqlserver 2005
Hi ,
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
Boolean in SQL Server 2005
What is the boolean equivalent for TRUE and FALSE in SQL Server 2005?
In Access true = -1 and false = 0
What should I use in SQL Server bit values?0 = false; 1 = true|||Can I write statements like Match='TRUE' instead of Match=1?|||
No, you need to use 0 and 1. SQL Server has no knowledge of true and false. This is only possible by creating custom data type via .NET.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi,but this is not recommended, you should really use the internal used data types for this simple operations. Your application should transfer the value to refelect the appropiate SQL Statements and present the data types the way you want the users to see them.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||why you want to do this. Do you have requirment to write a one code for both SQL Server and Access
boolean function
It is possible to create a function that returns a boolean value in t-sql?
A "boolean" function in t-sql would use the "bit" datatype; an example might be something like:
create function dbo.reflectBit (@.prm_bit bit)
returns bit
beginreturn (@.prm_bit)
end
go
select dbo.reflectBit (1) as aBit
-- aBit
-- -
-- 1
Give a look at the "bit datatype" article in books online.
Boolean formatting in report
I have a data source with a column that is a boolean data type. When
I render this data on the report the value of 0 displays as "False"
and 1 displays as "True". I would like 0 to display as "No" and 1 to
display as "Yes". I am assuming I have to set up an IIF statement
that evaluates the value and changes the display based on that. I was
wondering if anyone knew of a better way, such as a Custom Format
value that would be a better route?
Thanks in advance for any assistance!On Sep 20, 2:03 pm, midas <mcneeley1...@.yahoo.com> wrote:
> I have a data source with a column that is a boolean data type. When
> I render this data on the report the value of 0 displays as "False"
> and 1 displays as "True". I would like 0 to display as "No" and 1 to
> display as "Yes". I am assuming I have to set up an IIF statement
> that evaluates the value and changes the display based on that. I was
> wondering if anyone knew of a better way, such as a Custom Format
> value that would be a better route?
> Thanks in advance for any assistance!
The way you mentioned is quick and the effort is negligible.
=iif(Fields!SomeField.Value = True, "Yes", "No") -or- =iif(Fields!
SomeField.Value = 1, "Yes", "No")
You could also try using a case statement in the query/stored
procedure that is sourcing the report.
select case when SomeField = 'True' then 'Yes' else 'No' end as
SomeField, ... from table_x ...
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, I went with the case. I was just trying to be lazy. ;)|||On Sep 21, 2:57 pm, midas <mcneeley1...@.yahoo.com> wrote:
> Thanks, I went with the case. I was just trying to be lazy. ;)
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql
I render this data on the report the value of 0 displays as "False"
and 1 displays as "True". I would like 0 to display as "No" and 1 to
display as "Yes". I am assuming I have to set up an IIF statement
that evaluates the value and changes the display based on that. I was
wondering if anyone knew of a better way, such as a Custom Format
value that would be a better route?
Thanks in advance for any assistance!On Sep 20, 2:03 pm, midas <mcneeley1...@.yahoo.com> wrote:
> I have a data source with a column that is a boolean data type. When
> I render this data on the report the value of 0 displays as "False"
> and 1 displays as "True". I would like 0 to display as "No" and 1 to
> display as "Yes". I am assuming I have to set up an IIF statement
> that evaluates the value and changes the display based on that. I was
> wondering if anyone knew of a better way, such as a Custom Format
> value that would be a better route?
> Thanks in advance for any assistance!
The way you mentioned is quick and the effort is negligible.
=iif(Fields!SomeField.Value = True, "Yes", "No") -or- =iif(Fields!
SomeField.Value = 1, "Yes", "No")
You could also try using a case statement in the query/stored
procedure that is sourcing the report.
select case when SomeField = 'True' then 'Yes' else 'No' end as
SomeField, ... from table_x ...
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, I went with the case. I was just trying to be lazy. ;)|||On Sep 21, 2:57 pm, midas <mcneeley1...@.yahoo.com> wrote:
> Thanks, I went with the case. I was just trying to be lazy. ;)
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql
boolean fields displayed as true or false
I have a bit field in my data source that displays as true/false on my
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?
Tuesday, March 27, 2012
Boolean expression in SQL
Hello,
In VB/VB.NET, I can use an expression as such to evaluate true/false:
Dim blnValue As Boolean = (SomeObject.Property = "Some value")
Can I do this in T-SQL?
declare @.var bit
set @.var = ?
Thanks.
Bit type data can hold either a 1 or 0. So you have to set it to either 1 or 0. You might use it like boolean. One bit value will occupy 1 bytes of space.
Hope this helps.sql
In VB/VB.NET, I can use an expression as such to evaluate true/false:
Dim blnValue As Boolean = (SomeObject.Property = "Some value")
Can I do this in T-SQL?
declare @.var bit
set @.var = ?
Thanks.
T-SQL uses ANSI SQL CASE statement for Boolean expression, try the links below for more info. Hope this helps.
http://builder.com.com/5100-6388-5078041.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp
|||bmains wrote:
Hello,
In VB/VB.NET, I can use an expression as such to evaluate true/false:
Dim blnValue As Boolean = (SomeObject.Property = "Some value")
Can I do this in T-SQL?
declare @.var bit
set @.var = ?
Thanks.
Bit type data can hold either a 1 or 0. So you have to set it to either 1 or 0. You might use it like boolean. One bit value will occupy 1 bytes of space.
Hope this helps.sql
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean datatype in SQLServer 2005?
I thought there was one at long, long last, but I don't see it!
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
Josh
Hi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
Josh
Hi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>
Boolean datatype in SQLServer 2005?
I thought there was one at long, long last, but I don't see it!
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
JoshHi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.
4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>sql
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
JoshHi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.
4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>sql
Boolean datatype in SQLServer 2005?
I thought there was one at long, long last, but I don't see it!
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
JoshHi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>
Good grief, is this possible?
SQL_Variant ... nope.
Yes, C#, but really.
JoshHi Josh,
What's wrong with the bit type. It's been there for ever
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:lit3q1pom5csn3lmf8m1vvq1aelqm15f4n@.4ax.com...
>I thought there was one at long, long last, but I don't see it!
> Good grief, is this possible?
> SQL_Variant ... nope.
> Yes, C#, but really.
> Josh
>
Boolean Data Type not available (workaround for checkbox.checked storing?)
I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not:
<asp:CheckBoxID="CheckBox2"runat="server"Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>'Enabled="False"/>
This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display.
I checked the SQL statement, and sure enough, it has the
UpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @.Shipping... WHERE [PartID] = @.original_PartID
After fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.
Any insight as to how to get this to work is much appreciated. Thanks in advance.
I don't know if this will work declaratively but your problem is ANSI SQL Boolean is three valued True/False/Null the reason ANSI SQL is called three valued logic. The links below will show how to use Nullable types of FCL(framework class library) 2.0 to solve your problem. Hope this helps.
http://www.codeproject.com/csharp/c__20_nullable_types.asp
http://www.c-sharpcorner.com/UploadFile/PashuSX/NullableTypes04282006114548AM/NullableTypes.aspx?ArticleID=b28a5114-a92a-4ced-a23c-06d8a29de6e4
|||I found this thread:http://forums.asp.net/thread/1092916.aspx and after reading it, I've changed the data type in the database to Bit. This has significantly helped my problem .
BUT I have the checkbox in normal mode displaying perfectly fine. However, when I click edit, there is a textbox that displays either True or False (True is 1 and False is 0 in the database). If I change True to 0 or False to 1, it works. HOWEVER I want it to display a textbox. When I delete the textbox in the edit template, replace it with a checkbox and then set the databinding to the shipping field, I can click edit and the box appears just fine, but when I change it and click update, I get this Error:Syntax error converting the nvarchar value 'False' to a column of data type bit.Which to me means that it is trying to post the checkbox.checked ='s value, not just checkbox.checked which is stored as a bit in the database.
How/Where can I change this?
|||Figured it out! Turns out the auto generated update statement set the paramenter to<asp:ParameterName="Shipping"Type="String"/>
So I took out the Type="String" and it fixed my problem.
Thanks.
|||I am glad to see your problem is resolved.
Subscribe to:
Posts (Atom)