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.

IsNull issue

5 posts in General Discussion Last posting was on 2009-06-29 19:42:17.0Z
gchq Posted on 2009-06-29 18:12:28.0Z
Sender: 4715.4a49033f.846930886@sybase.com
From: gchq
Newsgroups: ianywhere.public.general
Subject: IsNull issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a49040c.47af.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 29 Jun 2009 11:12:28 -0700
X-Trace: forums-3-dub.sybase.com 1246299148 10.22.241.188 (29 Jun 2009 11:12:28 -0700)
X-Original-Trace: 29 Jun 2009 11:12:28 -0700, forums-3-dub.sybase.com
Lines: 14
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7628
Article PK: 6018

Hi there

Is there a reason why the following

SELECT Connection_ID, HOA_Name, (SELECT IsNull (Login_ID, 0)
as Login_ID FROM HOA_Managers WHERE HOA_ID = Connection_ID)
FROM HOA_Connections

still returns NULL as the value for Login_ID?

I've tried all sorts of variations on the theme - but still
the same...

Thanks in advance


"Kory Hodgson (Sybase iAnywhere)" <khodgson Posted on 2009-06-29 18:53:11.0Z
From: "Kory Hodgson (Sybase iAnywhere)" <khodgson@A_SPAM_FREE_sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: IsNull issue
References: <4a49040c.47af.1681692777@sybase.com>
In-Reply-To: <4a49040c.47af.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a490d97$3@forums-3-dub.sybase.com>
Date: 29 Jun 2009 11:53:11 -0700
X-Trace: forums-3-dub.sybase.com 1246301591 10.22.241.152 (29 Jun 2009 11:53:11 -0700)
X-Original-Trace: 29 Jun 2009 11:53:11 -0700, vip152.sybase.com
Lines: 22
X-Authenticated-User: techsupp
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7629
Article PK: 6023

What version and build of SQL Anywhere are you using?

I tried this on version 11.0.1.2044 and it seemed to work as expected,
no NULL values.

gchq wrote:
> Hi there
>
> Is there a reason why the following
>
> SELECT Connection_ID, HOA_Name, (SELECT IsNull (Login_ID, 0)
> as Login_ID FROM HOA_Managers WHERE HOA_ID = Connection_ID)
> FROM HOA_Connections
>
> still returns NULL as the value for Login_ID?
>
> I've tried all sorts of variations on the theme - but still
> the same...
>
> Thanks in advance


gchq Posted on 2009-06-29 18:57:02.0Z
Sender: 4e5d.4a490d0c.1804289383@sybase.com
From: gchq
Newsgroups: ianywhere.public.general
Subject: Re: IsNull issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a490e7e.4f5c.1681692777@sybase.com>
References: <4a49040c.47af.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 29 Jun 2009 11:57:02 -0700
X-Trace: forums-3-dub.sybase.com 1246301822 10.22.241.188 (29 Jun 2009 11:57:02 -0700)
X-Original-Trace: 29 Jun 2009 11:57:02 -0700, forums-3-dub.sybase.com
Lines: 20
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7630
Article PK: 6024


> Hi there
>
> Is there a reason why the following
>
> SELECT Connection_ID, HOA_Name, (SELECT IsNull (Login_ID,
> 0) as Login_ID FROM HOA_Managers WHERE HOA_ID =
> Connection_ID) FROM HOA_Connections
>
> still returns NULL as the value for Login_ID?
>
> I've tried all sorts of variations on the theme - but
> still the same...
>
> Thanks in advance

Got there

SELECT Connection_ID, HOA_Name, IsNull((SELECT IsNull
(Login_ID, 0) FROM HOA_Managers WHERE HOA_ID =
Connection_ID), 0) as Login_ID FROM HOA_Connections


Adi Nicoara Posted on 2009-06-29 19:42:17.0Z
From: Adi Nicoara <anicoara@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: IsNull issue
References: <4a49040c.47af.1681692777@sybase.com> <4a490e7e.4f5c.1681692777@sybase.com>
In-Reply-To: <4a490e7e.4f5c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a491919@forums-3-dub.sybase.com>
Date: 29 Jun 2009 12:42:17 -0700
X-Trace: forums-3-dub.sybase.com 1246304537 10.22.241.152 (29 Jun 2009 12:42:17 -0700)
X-Original-Trace: 29 Jun 2009 12:42:17 -0700, vip152.sybase.com
Lines: 40
X-Authenticated-User: techsupp
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7632
Article PK: 6022


gchq wrote:
>> Hi there
>>
>> Is there a reason why the following
>>
>> SELECT Connection_ID, HOA_Name, (SELECT IsNull (Login_ID,
>> 0) as Login_ID FROM HOA_Managers WHERE HOA_ID =
>> Connection_ID) FROM HOA_Connections
>>
>> still returns NULL as the value for Login_ID?
>>
>> I've tried all sorts of variations on the theme - but
>> still the same...
>>
>> Thanks in advance
>
> Got there
>
> SELECT Connection_ID, HOA_Name, IsNull((SELECT IsNull
> (Login_ID, 0) FROM HOA_Managers WHERE HOA_ID =
> Connection_ID), 0) as Login_ID FROM HOA_Connections

I believe that the problem is in your subquery:

SELECT IsNull (Login_ID, 0) FROM HOA_Managers WHERE HOA_ID =Connection_ID

Although the IsNull function will return either Login_ID or 0, because
of your where clause, the select statement might not return anything -
no rows match your condition, thus giving you the null value. You indeed
solved that by adding another IsNull function outside the subquery, but
this might be a bit complicated.

A better alternative to your subquery would be ( and please test this
out - I do not know your schema, so I am assuming here):

SELECT Connection_ID, HOA_Name, IsNull (Login_ID, 0) as Login_ID FROM
HOA_Managers,HOA_Connections WHERE HOA_ID = Connection_ID

Cheers,
Adi