Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

What is "IS TRUE" good for...?

9 posts in General Discussion (old) Last posting was on 2008-09-19 14:35:26.0Z
Markus KARG Posted on 2008-09-16 12:43:17.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
Subject: What is "IS TRUE" good for...?
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48cfa9e5$1@forums-1-dub>
Date: 16 Sep 2008 05:43:17 -0700
X-Trace: forums-1-dub 1221568997 10.22.241.152 (16 Sep 2008 05:43:17 -0700)
X-Original-Trace: 16 Sep 2008 05:43:17 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:162
Article PK: 866608

I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0. By
incident I found the keyword "IS TRUE" and wonder what it is good for. The
only example I could find in the docs is "5*3=15 IS TRUE". I do not get the
idea of this. Can anybody give a real world SQL example, what IS TRUE's
benefit is?

I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What additional
use does the "IS TRUE" keyword introduce?

Also I'd like to know why on the other hand I cannot use "?1 IS TRUE" or "?1
= TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a benefit...
:-)

Thanks!
Markus


"Frank Ploessel" <fpl... Posted on 2008-09-16 13:04:33.0Z
Subject: Re: What is "IS TRUE" good for...?
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub>
Content-Transfer-Encoding: 8bit
Message-ID: <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 16 Sep 2008 06:04:33 -0700
X-Trace: forums-1-dub 1221570273 10.22.241.152 (16 Sep 2008 06:04:33 -0700)
X-Original-Trace: 16 Sep 2008 06:04:33 -0700, vip152.sybase.com
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:163
Article PK: 866609

Markus,

In fact, SQL does not have two valued logic with TRUE and FALSE as many
other programming languages, but three valued logic with TRUE, FALSE and
UNKNOWN. The IS keyword helps you to deal with this, especially to exclude
or include the unkown value. The UNKNON originates from expressions
containing NULL values in most cases: e. g.
C = 1
would be unknown (not false nor true) if column C is null.

See
SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL value
and
SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
conditions - Three-valued logic
in the documentation. The latter contains a truth table for the IS keyword.

You cannot pass booleans to a SQL database, as there is no boolean
datatype for e. g. variables or columns. You can only have expressions of
boolean type, in the documentation that is what is called "search
condition".

What you could do to pass "kind of booleans" to a statement would be
something like
WHERE ?1 = 1
and then either pass a one or a zero as the parameter.

Frank

Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:

> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
> By
> incident I found the keyword "IS TRUE" and wonder what it is good for.
> The
> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
> the
> idea of this. Can anybody give a real world SQL example, what IS TRUE's
> benefit is?
>
> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
> additional
> use does the "IS TRUE" keyword introduce?
>
> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE" or
> "?1
> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a benefit...
> :-)
>
> Thanks!
> Markus
>
>


Markus KARG Posted on 2008-09-18 14:02:17.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com>
Subject: Re: What is "IS TRUE" good for...?
Lines: 76
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48d25f69@forums-1-dub>
Date: 18 Sep 2008 07:02:17 -0700
X-Trace: forums-1-dub 1221746537 10.22.241.152 (18 Sep 2008 07:02:17 -0700)
X-Original-Trace: 18 Sep 2008 07:02:17 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:164
Article PK: 866612

Frank,

I already have read the documentation and I am quite familiar with the truth
table and three valued logic and all that.

My actual question is: For what in the real world shall it be good for? I
mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
true. I will not get the rows where C is null or C is not 1. So the
additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
returns exactly the same result as "WHERE C = 1 IS TRUE".

THAT is what I do not understand. The real world use. Not the theory.

Thanks
Markus


"Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im Newsbeitrag
news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...

> Markus,
>
> In fact, SQL does not have two valued logic with TRUE and FALSE as many
> other programming languages, but three valued logic with TRUE, FALSE and
> UNKNOWN. The IS keyword helps you to deal with this, especially to exclude
> or include the unkown value. The UNKNON originates from expressions
> containing NULL values in most cases: e. g.
> C = 1
> would be unknown (not false nor true) if column C is null.
>
> See
> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL value
> and
> SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
> conditions - Three-valued logic
> in the documentation. The latter contains a truth table for the IS
> keyword.
>
> You cannot pass booleans to a SQL database, as there is no boolean
> datatype for e. g. variables or columns. You can only have expressions of
> boolean type, in the documentation that is what is called "search
> condition".
>
> What you could do to pass "kind of booleans" to a statement would be
> something like
> WHERE ?1 = 1
> and then either pass a one or a zero as the parameter.
>
> Frank
>
> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>
>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
>> By
>> incident I found the keyword "IS TRUE" and wonder what it is good for.
>> The
>> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
>> the
>> idea of this. Can anybody give a real world SQL example, what IS TRUE's
>> benefit is?
>>
>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>> additional
>> use does the "IS TRUE" keyword introduce?
>>
>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE" or
>> "?1
>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a benefit...
>> :-)
>>
>> Thanks!
>> Markus
>>
>>
>


Ivan T. Bowman Posted on 2008-09-19 01:18:31.0Z
From: "Ivan T. Bowman" <ibowman@ianywhere.NOSPAM.com>
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub>
Subject: Re: What is "IS TRUE" good for...?
Lines: 113
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48d2fde7@forums-1-dub>
Date: 18 Sep 2008 18:18:31 -0700
X-Trace: forums-1-dub 1221787111 10.22.241.152 (18 Sep 2008 18:18:31 -0700)
X-Original-Trace: 18 Sep 2008 18:18:31 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:165
Article PK: 866610

In many contexts, predicates are "false interpreted". As you point out, this
means that UNKOWN and FALSE have the same result.

There are some contexts that are TRUE interpreted. For example, CHECK
constraints do not reject the insert/update if the CHECK condition is
UNKNOWN.

The following passes without a murmur:

create table T( x int check( x > 5 ) );
insert into T values(NULL)

In these TRUE interpreted contexts, you may wish to treat UNKNOWN as FALSE.
So, you could write:

create table T( x int check( x > 5 is true ) )

Another example is the IF expression. If the predicate in the IF expression
is UNKNOWN, the IF expression returns NULL, not the ELSE clause value.
The following returns NULL.
select if NULL = 1 is true then 1 else 2 end if

If you wish to return 2 instead, you can use IS TRUE.

These two examples are not an exhaustive list of TRUE interpreted contexts
in the server.

--
Ivan T. Bowman
SQL Anywhere Research and Development

"Markus KARG" <karg@quipsy.de> wrote in message
news:48d25f69@forums-1-dub...
> Frank,
>
> I already have read the documentation and I am quite familiar with the
> truth table and three valued logic and all that.
>
> My actual question is: For what in the real world shall it be good for? I
> mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
> true. I will not get the rows where C is null or C is not 1. So the
> additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
> returns exactly the same result as "WHERE C = 1 IS TRUE".
>
> THAT is what I do not understand. The real world use. Not the theory.
>
> Thanks
> Markus
>
>
> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
> Newsbeitrag news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...
>> Markus,
>>
>> In fact, SQL does not have two valued logic with TRUE and FALSE as many
>> other programming languages, but three valued logic with TRUE, FALSE and
>> UNKNOWN. The IS keyword helps you to deal with this, especially to
>> exclude or include the unkown value. The UNKNON originates from
>> expressions containing NULL values in most cases: e. g.
>> C = 1
>> would be unknown (not false nor true) if column C is null.
>>
>> See
>> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL
>> value
>> and
>> SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
>> conditions - Three-valued logic
>> in the documentation. The latter contains a truth table for the IS
>> keyword.
>>
>> You cannot pass booleans to a SQL database, as there is no boolean
>> datatype for e. g. variables or columns. You can only have expressions of
>> boolean type, in the documentation that is what is called "search
>> condition".
>>
>> What you could do to pass "kind of booleans" to a statement would be
>> something like
>> WHERE ?1 = 1
>> and then either pass a one or a zero as the parameter.
>>
>> Frank
>>
>> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>
>>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
>>> By
>>> incident I found the keyword "IS TRUE" and wonder what it is good for.
>>> The
>>> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
>>> the
>>> idea of this. Can anybody give a real world SQL example, what IS TRUE's
>>> benefit is?
>>>
>>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>>> additional
>>> use does the "IS TRUE" keyword introduce?
>>>
>>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE" or
>>> "?1
>>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a
>>> benefit...
>>> :-)
>>>
>>> Thanks!
>>> Markus
>>>
>>>
>>
>
>


"Frank Ploessel" <fpl... Posted on 2008-09-19 11:50:29.0Z
Subject: Re: What is "IS TRUE" good for...?
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub> <48d2fde7@forums-1-dub>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uhp8ieu1j0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Sep 2008 04:50:29 -0700
X-Trace: forums-1-dub 1221825029 10.22.241.152 (19 Sep 2008 04:50:29 -0700)
X-Original-Trace: 19 Sep 2008 04:50:29 -0700, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:167
Article PK: 866613

Ivan,

I agree with what you write, but I suppose you wanted to state:

The following returns NULL.
select if NULL = 1 then 1 else 2 endif
and the following 2.
select if NULL = 1 is true then 1 else 2 endif


I also changed the "end if" to "endif" so this also works with pre-11
versions of ASA, as this is the if expression, not the if statement.

Frank


Am 19.09.2008, 03:18 Uhr, schrieb Ivan T. Bowman
<ibowman@ianywhere.nospam.com>:

> The following returns NULL.
> select if NULL = 1 is true then 1 else 2 end if


Markus KARG Posted on 2008-09-19 13:09:03.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub> <48d2fde7@forums-1-dub>
Subject: Re: What is "IS TRUE" good for...?
Lines: 121
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48d3a46f@forums-1-dub>
Date: 19 Sep 2008 06:09:03 -0700
X-Trace: forums-1-dub 1221829743 10.22.241.152 (19 Sep 2008 06:09:03 -0700)
X-Original-Trace: 19 Sep 2008 06:09:03 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:169
Article PK: 866615

So is there a list somewhere in what context NULL is interpreted TRUE /
FALSE ?

"Ivan T. Bowman" <ibowman@ianywhere.NOSPAM.com> schrieb im Newsbeitrag
news:48d2fde7@forums-1-dub...

> In many contexts, predicates are "false interpreted". As you point out,
> this means that UNKOWN and FALSE have the same result.
>
> There are some contexts that are TRUE interpreted. For example, CHECK
> constraints do not reject the insert/update if the CHECK condition is
> UNKNOWN.
>
> The following passes without a murmur:
>
> create table T( x int check( x > 5 ) );
> insert into T values(NULL)
>
> In these TRUE interpreted contexts, you may wish to treat UNKNOWN as
> FALSE. So, you could write:
>
> create table T( x int check( x > 5 is true ) )
>
> Another example is the IF expression. If the predicate in the IF
> expression is UNKNOWN, the IF expression returns NULL, not the ELSE clause
> value.
> The following returns NULL.
> select if NULL = 1 is true then 1 else 2 end if
>
> If you wish to return 2 instead, you can use IS TRUE.
>
> These two examples are not an exhaustive list of TRUE interpreted contexts
> in the server.
>
> --
> Ivan T. Bowman
> SQL Anywhere Research and Development
>
> "Markus KARG" <karg@quipsy.de> wrote in message
> news:48d25f69@forums-1-dub...
>> Frank,
>>
>> I already have read the documentation and I am quite familiar with the
>> truth table and three valued logic and all that.
>>
>> My actual question is: For what in the real world shall it be good for? I
>> mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
>> true. I will not get the rows where C is null or C is not 1. So the
>> additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
>> returns exactly the same result as "WHERE C = 1 IS TRUE".
>>
>> THAT is what I do not understand. The real world use. Not the theory.
>>
>> Thanks
>> Markus
>>
>>
>> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
>> Newsbeitrag news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...
>>> Markus,
>>>
>>> In fact, SQL does not have two valued logic with TRUE and FALSE as many
>>> other programming languages, but three valued logic with TRUE, FALSE and
>>> UNKNOWN. The IS keyword helps you to deal with this, especially to
>>> exclude or include the unkown value. The UNKNON originates from
>>> expressions containing NULL values in most cases: e. g.
>>> C = 1
>>> would be unknown (not false nor true) if column C is null.
>>>
>>> See
>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL
>>> value
>>> and
>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
>>> conditions - Three-valued logic
>>> in the documentation. The latter contains a truth table for the IS
>>> keyword.
>>>
>>> You cannot pass booleans to a SQL database, as there is no boolean
>>> datatype for e. g. variables or columns. You can only have expressions
>>> of boolean type, in the documentation that is what is called "search
>>> condition".
>>>
>>> What you could do to pass "kind of booleans" to a statement would be
>>> something like
>>> WHERE ?1 = 1
>>> and then either pass a one or a zero as the parameter.
>>>
>>> Frank
>>>
>>> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>>
>>>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
>>>> By
>>>> incident I found the keyword "IS TRUE" and wonder what it is good for.
>>>> The
>>>> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
>>>> the
>>>> idea of this. Can anybody give a real world SQL example, what IS TRUE's
>>>> benefit is?
>>>>
>>>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>>>> additional
>>>> use does the "IS TRUE" keyword introduce?
>>>>
>>>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE"
>>>> or "?1
>>>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a
>>>> benefit...
>>>> :-)
>>>>
>>>> Thanks!
>>>> Markus
>>>>
>>>>
>>>
>>
>>
>
>


"Frank Ploessel" <fpl... Posted on 2008-09-19 14:35:26.0Z
Subject: Re: What is "IS TRUE" good for...?
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub> <48d2fde7@forums-1-dub> <48d3a46f@forums-1-dub>
Content-Transfer-Encoding: 8bit
Message-ID: <op.uhqf5at4j0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Sep 2008 07:35:26 -0700
X-Trace: forums-1-dub 1221834926 10.22.241.152 (19 Sep 2008 07:35:26 -0700)
X-Original-Trace: 19 Sep 2008 07:35:26 -0700, vip152.sybase.com
Lines: 154
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:171
Article PK: 866617

Markus,

The general rule of thumb for all types of SQL dialects is that for WHERE
and HAVING, only TRUE passes the rule, and that one NULL/UNKNOWN value
makes the whole expression using it UNKNOWN, the big exception being
aggregation functions which just ignore NULLs and only deliver a NULL
result if all aggregated records had NULL.

There are many special cases like ASA treating NULL as an empty string in
string concatenation, and the list aggregation function delivering an
empty string for the aggregation of NULL input instead of NULL. On the
other side, older Oracle versions were treating empty strings as NULL, i.
e. resulting in UNKNOWN expressions when using them.

You are right, a general overview of where NULL/UNKNOWN is treated how in
ASA would be helpful.

Frank


Am 19.09.2008, 15:09 Uhr, schrieb Markus KARG <karg@quipsy.de>:

> So is there a list somewhere in what context NULL is interpreted TRUE /
> FALSE ?
>
> "Ivan T. Bowman" <ibowman@ianywhere.NOSPAM.com> schrieb im Newsbeitrag
> news:48d2fde7@forums-1-dub...
>> In many contexts, predicates are "false interpreted". As you point out,
>> this means that UNKOWN and FALSE have the same result.
>>
>> There are some contexts that are TRUE interpreted. For example, CHECK
>> constraints do not reject the insert/update if the CHECK condition is
>> UNKNOWN.
>>
>> The following passes without a murmur:
>>
>> create table T( x int check( x > 5 ) );
>> insert into T values(NULL)
>>
>> In these TRUE interpreted contexts, you may wish to treat UNKNOWN as
>> FALSE. So, you could write:
>>
>> create table T( x int check( x > 5 is true ) )
>>
>> Another example is the IF expression. If the predicate in the IF
>> expression is UNKNOWN, the IF expression returns NULL, not the ELSE
>> clause
>> value.
>> The following returns NULL.
>> select if NULL = 1 is true then 1 else 2 end if
>>
>> If you wish to return 2 instead, you can use IS TRUE.
>>
>> These two examples are not an exhaustive list of TRUE interpreted
>> contexts
>> in the server.
>>
>> --
>> Ivan T. Bowman
>> SQL Anywhere Research and Development
>>
>> "Markus KARG" <karg@quipsy.de> wrote in message
>> news:48d25f69@forums-1-dub...
>>> Frank,
>>>
>>> I already have read the documentation and I am quite familiar with the
>>> truth table and three valued logic and all that.
>>>
>>> My actual question is: For what in the real world shall it be good
>>> for? I
>>> mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
>>> true. I will not get the rows where C is null or C is not 1. So the
>>> additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
>>> returns exactly the same result as "WHERE C = 1 IS TRUE".
>>>
>>> THAT is what I do not understand. The real world use. Not the theory.
>>>
>>> Thanks
>>> Markus
>>>
>>>
>>> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
>>> Newsbeitrag news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...
>>>> Markus,
>>>>
>>>> In fact, SQL does not have two valued logic with TRUE and FALSE as
>>>> many
>>>> other programming languages, but three valued logic with TRUE, FALSE
>>>> and
>>>> UNKNOWN. The IS keyword helps you to deal with this, especially to
>>>> exclude or include the unkown value. The UNKNON originates from
>>>> expressions containing NULL values in most cases: e. g.
>>>> C = 1
>>>> would be unknown (not false nor true) if column C is null.
>>>>
>>>> See
>>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL
>>>> value
>>>> and
>>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements >
>>>> Search
>>>> conditions - Three-valued logic
>>>> in the documentation. The latter contains a truth table for the IS
>>>> keyword.
>>>>
>>>> You cannot pass booleans to a SQL database, as there is no boolean
>>>> datatype for e. g. variables or columns. You can only have expressions
>>>> of boolean type, in the documentation that is what is called "search
>>>> condition".
>>>>
>>>> What you could do to pass "kind of booleans" to a statement would be
>>>> something like
>>>> WHERE ?1 = 1
>>>> and then either pass a one or a zero as the parameter.
>>>>
>>>> Frank
>>>>
>>>> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>>>
>>>>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is
>>>>> 0.
>>>>> By
>>>>> incident I found the keyword "IS TRUE" and wonder what it is good
>>>>> for.
>>>>> The
>>>>> only example I could find in the docs is "5*3=15 IS TRUE". I do not
>>>>> get
>>>>> the
>>>>> idea of this. Can anybody give a real world SQL example, what IS
>>>>> TRUE's
>>>>> benefit is?
>>>>>
>>>>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>>>>> additional
>>>>> use does the "IS TRUE" keyword introduce?
>>>>>
>>>>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE"
>>>>> or "?1
>>>>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a
>>>>> benefit...
>>>>> :-)
>>>>>
>>>>> Thanks!
>>>>> Markus
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>
>


"Frank Ploessel" <fpl... Posted on 2008-09-19 11:46:03.0Z
Subject: Re: What is "IS TRUE" good for...?
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub>
Content-Transfer-Encoding: 8bit
Message-ID: <op.uhp8a0g1j0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Sep 2008 04:46:03 -0700
X-Trace: forums-1-dub 1221824763 10.22.241.152 (19 Sep 2008 04:46:03 -0700)
X-Original-Trace: 19 Sep 2008 04:46:03 -0700, vip152.sybase.com
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:166
Article PK: 866611

Markus,

I agree that in 90% of time, you do not need the IS TRUE/FALSE/UNKNOWN
predicate.

The main use of the IS predicate is to reduce the three valued SQL logic
to the two valued Boolean logic that most developers are used to.

And I know many people prefer using constructs like
coalesce(numeric expression, 0) <> 0
to avoid issues with NULLs in the expression instead of IS predicates like
numeric expression <> 0 IS TRUE

But this is a matter of taste and custom. It may just not seem natural for
some developers to apply a Boolean expression to a Boolean expression; and
you can get the same results both ways.
And maybe the best way to avoid many of the problems with unknown values
and three valued logic is to use a NOT NULL constraint whereever possible.

Frank

Am 18.09.2008, 16:02 Uhr, schrieb Markus KARG <karg@quipsy.de>:

> Frank,
>
> I already have read the documentation and I am quite familiar with the
> truth
> table and three valued logic and all that.
>
> My actual question is: For what in the real world shall it be good for? I
> mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
> true. I will not get the rows where C is null or C is not 1. So the
> additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
> returns exactly the same result as "WHERE C = 1 IS TRUE".
>
> THAT is what I do not understand. The real world use. Not the theory.
>
> Thanks
> Markus
>
>
> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
> Newsbeitrag
> news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...
>> Markus,
>>
>> In fact, SQL does not have two valued logic with TRUE and FALSE as many
>> other programming languages, but three valued logic with TRUE, FALSE and
>> UNKNOWN. The IS keyword helps you to deal with this, especially to
>> exclude
>> or include the unkown value. The UNKNON originates from expressions
>> containing NULL values in most cases: e. g.
>> C = 1
>> would be unknown (not false nor true) if column C is null.
>>
>> See
>> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL
>> value
>> and
>> SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
>> conditions - Three-valued logic
>> in the documentation. The latter contains a truth table for the IS
>> keyword.
>>
>> You cannot pass booleans to a SQL database, as there is no boolean
>> datatype for e. g. variables or columns. You can only have expressions
>> of
>> boolean type, in the documentation that is what is called "search
>> condition".
>>
>> What you could do to pass "kind of booleans" to a statement would be
>> something like
>> WHERE ?1 = 1
>> and then either pass a one or a zero as the parameter.
>>
>> Frank
>>
>> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>
>>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
>>> By
>>> incident I found the keyword "IS TRUE" and wonder what it is good for.
>>> The
>>> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
>>> the
>>> idea of this. Can anybody give a real world SQL example, what IS TRUE's
>>> benefit is?
>>>
>>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>>> additional
>>> use does the "IS TRUE" keyword introduce?
>>>
>>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE"
>>> or
>>> "?1
>>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a
>>> benefit...
>>> :-)
>>>
>>> Thanks!
>>> Markus
>>>
>>>
>>
>
>


Markus KARG Posted on 2008-09-19 13:10:35.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48cfa9e5$1@forums-1-dub> <op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com> <48d25f69@forums-1-dub> <op.uhp8a0g1j0bybf@bonw01164.internal.imsglobal.com>
Subject: Re: What is "IS TRUE" good for...?
Lines: 116
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48d3a4cb$1@forums-1-dub>
Date: 19 Sep 2008 06:10:35 -0700
X-Trace: forums-1-dub 1221829835 10.22.241.152 (19 Sep 2008 06:10:35 -0700)
X-Original-Trace: 19 Sep 2008 06:10:35 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:170
Article PK: 866616

I see! Thanks for the explanation! :-)

Regards
Markus

"Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im Newsbeitrag
news:op.uhp8a0g1j0bybf@bonw01164.internal.imsglobal.com...

> Markus,
>
> I agree that in 90% of time, you do not need the IS TRUE/FALSE/UNKNOWN
> predicate.
>
> The main use of the IS predicate is to reduce the three valued SQL logic
> to the two valued Boolean logic that most developers are used to.
>
> And I know many people prefer using constructs like
> coalesce(numeric expression, 0) <> 0
> to avoid issues with NULLs in the expression instead of IS predicates like
> numeric expression <> 0 IS TRUE
>
> But this is a matter of taste and custom. It may just not seem natural for
> some developers to apply a Boolean expression to a Boolean expression; and
> you can get the same results both ways.
> And maybe the best way to avoid many of the problems with unknown values
> and three valued logic is to use a NOT NULL constraint whereever possible.
>
> Frank
>
> Am 18.09.2008, 16:02 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>
>> Frank,
>>
>> I already have read the documentation and I am quite familiar with the
>> truth
>> table and three valued logic and all that.
>>
>> My actual question is: For what in the real world shall it be good for? I
>> mean, when I do "WHERE C = 1" I will get only those rows where C = 1 is
>> true. I will not get the rows where C is null or C is not 1. So the
>> additional "IS TRUE" brings not additional benefit, since "WHERE C = 1"
>> returns exactly the same result as "WHERE C = 1 IS TRUE".
>>
>> THAT is what I do not understand. The real world use. Not the theory.
>>
>> Thanks
>> Markus
>>
>>
>> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
>> Newsbeitrag
>> news:op.uhkrxwgvj0bybf@bonw01164.internal.imsglobal.com...
>>> Markus,
>>>
>>> In fact, SQL does not have two valued logic with TRUE and FALSE as many
>>> other programming languages, but three valued logic with TRUE, FALSE and
>>> UNKNOWN. The IS keyword helps you to deal with this, especially to
>>> exclude
>>> or include the unkown value. The UNKNON originates from expressions
>>> containing NULL values in most cases: e. g.
>>> C = 1
>>> would be unknown (not false nor true) if column C is null.
>>>
>>> See
>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements - NULL
>>> value
>>> and
>>> SQL Anywhere® Server - SQL Reference > SQL Language Elements > Search
>>> conditions - Three-valued logic
>>> in the documentation. The latter contains a truth table for the IS
>>> keyword.
>>>
>>> You cannot pass booleans to a SQL database, as there is no boolean
>>> datatype for e. g. variables or columns. You can only have expressions
>>> of
>>> boolean type, in the documentation that is what is called "search
>>> condition".
>>>
>>> What you could do to pass "kind of booleans" to a statement would be
>>> something like
>>> WHERE ?1 = 1
>>> and then either pass a one or a zero as the parameter.
>>>
>>> Frank
>>>
>>> Am 16.09.2008, 14:43 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>>
>>>> I am used to dealing with boolean as BITs, so TRUE is 1 and FALSE is 0.
>>>> By
>>>> incident I found the keyword "IS TRUE" and wonder what it is good for.
>>>> The
>>>> only example I could find in the docs is "5*3=15 IS TRUE". I do not get
>>>> the
>>>> idea of this. Can anybody give a real world SQL example, what IS TRUE's
>>>> benefit is?
>>>>
>>>> I mean, I could just write: "SELECT * FROM T WHERE 5*3=15". What
>>>> additional
>>>> use does the "IS TRUE" keyword introduce?
>>>>
>>>> Also I'd like to know why on the other hand I cannot use "?1 IS TRUE"
>>>> or
>>>> "?1
>>>> = TRUE" and pass in Boolean.TRUE using JDBC... THAT would be a
>>>> benefit...
>>>> :-)
>>>>
>>>> Thanks!
>>>> Markus
>>>>
>>>>
>>>
>>
>>
>