Logical constant in Select

I can add a constant to any Select statement, SELECT 0 as xxx,* From XYZ, bu
t
how I can add a Logical constant?Hi,
What do you mean by local constant ? Please make it clear
Thanks
Hari
SQL Server MVP
"Constantin Chionas" <constantinchionas@.hotmail.com> wrote in message
news:B7ABAC44-4C44-4E74-A74F-F02B8944C58E@.microsoft.com...
>I can add a constant to any Select statement, SELECT 0 as xxx,* From XYZ,
>but
> how I can add a Logical constant?|||Are you talking about a Boolean constant (i.e., True, False)? SQL Server 2K
doesn't allow you to return a Boolean value in a result set. What you can
do is use a CHAR flag ('T' or 'F') to represent your Boolean flag values, or
one of the INTEGER types, or even a BIT (0/1).
"Constantin Chionas" <constantinchionas@.hotmail.com> wrote in message
news:B7ABAC44-4C44-4E74-A74F-F02B8944C58E@.microsoft.com...
>I can add a constant to any Select statement, SELECT 0 as xxx,* From XYZ,
>but
> how I can add a Logical constant?|||Thank you all for your help,
I guess after a lot of trying I reached to the same conclusion, I cannot
return a Boolean value, so I guess I will use the BIT (0/1) as I am already
doing, but I wanted to be sure that there is no other way.
"Michael C#" wrote:

> Are you talking about a Boolean constant (i.e., True, False)? SQL Server
2K
> doesn't allow you to return a Boolean value in a result set. What you can
> do is use a CHAR flag ('T' or 'F') to represent your Boolean flag values,
or
> one of the INTEGER types, or even a BIT (0/1).
> "Constantin Chionas" <constantinchionas@.hotmail.com> wrote in message
> news:B7ABAC44-4C44-4E74-A74F-F02B8944C58E@.microsoft.com...
>
>|||I've heard that ANSI SQL99 includes Boolean types, but someone else would
have to speak to whether they're included in SQL Server 2005 or not.
"Constantin Chionas" <constantinchionas@.hotmail.com> wrote in message
news:A09BE661-6E05-48ED-9D04-F986D93B98B5@.microsoft.com...
> Thank you all for your help,
> I guess after a lot of trying I reached to the same conclusion, I cannot
> return a Boolean value, so I guess I will use the BIT (0/1) as I am
> already
> doing, but I wanted to be sure that there is no other way.
> "Michael C#" wrote:
>|||There is no logical datatype in SQL, nor do good SQL programmers write
code that uses low-level bit flags. It sounds like you have serious
design issues.|||Let's assume for the moment that we define a boolean data type to mean an
integer limited to the values 0 and 1. Are you saying that all database doma
in
problems where this sort of value is used as a column is a de facto sign of
poor
design?
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113230885.195828.320500@.g14g2000cwa.googlegroups.com...
> There is no logical datatype in SQL, nor do good SQL programmers write
> code that uses low-level bit flags. It sounds like you have serious
> design issues.
>|||>> Let's assume for the moment that we define a boolean data type to
mean an integer limited to the values 0 and 1. Are you saying that all
database domain problems where this sort of value is used as a column
is a de facto sign of poor design? <<
Does this encoding allow for easy expansion of value? Nope. Does this
convention allow for interfaces to ANSI X3J programming languages which
have a BOOLEAN or LOGICAL data type? Nope (Even Microsoft's
proprietary languages use +1,0 and -1). Does it usually represent a
computation rather than a simple scalar value? Yep! So you use a CASE
expression in the SELECT list with that computation and a meaningful
output. Most often, an event date or attribute value would provide
more information than a simple low-level bit flag.|||> Does this encoding allow for easy expansion of value? Nope
Clarify. What type of expansion? Do you mean from 0,1 to 0..n? That breaks t
he
domain restriction and thus evades the issue. The values are restricted to t
wo
values and perhaps a null. Are you suggesting that boolean-type columns (mea
ning
columns restricted to two values and perhaps a null) have no purpose whatsoe
ver
in any good database design?

> Does this
> convention allow for interfaces to ANSI X3J programming languages which
> have a BOOLEAN or LOGICAL data type?
An irrelevant example. If the database product you are currently using does
not
provide for the BOOLEAN or LOGICAL data type, then migrating to one that doe
s
will require a conversion regardless of your current approach. Clearly ANSI
/ISO
feels the necessity of providing for the declaration of a flag-type data typ
e as
they added it to the SQL99 specification.

> Does it usually represent a
> computation rather than a simple scalar value?
Never use a noun when you can use a pronoun ;-> I'm not sure what "it" is an
d
I'm not sure what you mean by a "computation" as opposed to a simple scalar
value. If you mean is the flag "calculable" as opposed to static, then presu
me
no.

> So you use a CASE
> expression in the SELECT list with that computation and a meaningful
> output.
Are you talking about converting said boolean value into a meaning value lik
e
Yes/No or True/False? I was under the impression that was the job of the
reporting and/or presentation engines. It might display as Yes/No on one
report/screen, a checkbox on another, Enabled/Disabled on another and so on.

> Most often, an event date or attribute value would provide
> more information than a simple low-level bit flag.
How is it that this is "low-level"? Just because Microsoft used the word "bi
t"?
I could just as easily define a column as an Integer Check (Column In(0,1))
and
achieve the same result. The method with which the value is stored is, as yo
u
constantly remind everyone, an implementation detail.
I come back to the original question. Does a boolean-type column (one that i
s
restricted to two values and perhaps null) have a place in database design?
If
the answer is yes, then techniques such as SQL's BIT data type are valid
solutions to get around the fact that SQL 2000 does not provide for the BOOL
EAN
keyword and thus should not be criticized. There are valid reasons for not u
sing
a BIT field in SQL not the least of which is that you cannot include that co
lumn
in an index. However, that is an entirely different subject than precluding
the
use of boolean colums altogether. If boolean columns do not have a valid pla
ce
in database design, then I would numerous scenarios that I interested in kno
wing
how you would solve without a boolean.
Thomas