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.

Null value eliminitaed in aggregate function in Ver 9.0

5 posts in General Discussion Last posting was on 2004-01-22 14:33:56.0Z
Chandresh Patel Posted on 2004-01-21 18:29:16.0Z
Sender: 4a92.400ec42d.1804289383@sybase.com
From: Chandresh Patel
Newsgroups: ianywhere.public.general
Subject: Null value eliminitaed in aggregate function in Ver 9.0
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <400ec4fc.4a97.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jan 2004 10:29:16 -0800
X-Trace: forums-1-dub 1074709756 10.22.241.41 (21 Jan 2004 10:29:16 -0800)
X-Original-Trace: 21 Jan 2004 10:29:16 -0800, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2359
Article PK: 4565

I am using sybase anywhere 8.02 and after that i start to
upgrade all to 9.0 and finally my program give me error like

Null value eliminitaed in aggregate function

when i am using this query

select sum(One+two+Three) as NetSales from sales.

Advanced thank you for help.


Nick Elson Posted on 2004-01-21 19:33:47.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <400ec4fc.4a97.846930886@sybase.com>
Subject: Re: Null value eliminitaed in aggregate function in Ver 9.0
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <400ed65f$1@forums-2-dub>
X-Original-Trace: 21 Jan 2004 11:43:27 -0800, nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Jan 2004 11:17:52 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Jan 2004 11:33:47 -0800
X-Trace: forums-1-dub 1074713627 10.22.108.75 (21 Jan 2004 11:33:47 -0800)
X-Original-Trace: 21 Jan 2004 11:33:47 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2361
Article PK: 4570

In 'true' ANSI NULL behaviour

NULL != 0

and (possibly in your case)

1 + NULL + 3 = NULL

That warning probably means that

(One+two+Three) IS NULL

[for one or more of your result set rows]
and those have been eliminated by the

sum( )

aggregate function.

What you probably want to/need to consider doing
is using the IFNULL( ) function provided something
like

select sum(ifnull("One",0,"One")+
ifnull("two",0,"two")+
ifnull("Three",0,"Three")) as NetSales
from sales.

Alternatively you can change your schema to default to 0.0
and not allow nulls into your data.

<Chandresh Patel> wrote in message
news:400ec4fc.4a97.846930886@sybase.com...
> I am using sybase anywhere 8.02 and after that i start to
> upgrade all to 9.0 and finally my program give me error like
>
> Null value eliminitaed in aggregate function
>
> when i am using this query
>
> select sum(One+two+Three) as NetSales from sales.
>
> Advanced thank you for help.


Chris Keating (iAnywhere Solutions) Posted on 2004-01-21 21:29:16.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <400ec4fc.4a97.846930886@sybase.com> <400ed65f$1@forums-2-dub>
Subject: Re: Null value eliminitaed in aggregate function in Ver 9.0
Lines: 82
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: keating-xp.sybase.com
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <400eef2c$1@forums-1-dub>
Date: 21 Jan 2004 13:29:16 -0800
X-Trace: forums-1-dub 1074720556 172.31.141.1 (21 Jan 2004 13:29:16 -0800)
X-Original-Trace: 21 Jan 2004 13:29:16 -0800, keating-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2363
Article PK: 4567

This is a warning not an error. You can choose to ignore the warning. If you
wish for you app not to get the warning, check "Suppress fetch warnings" in
your ODBC DSN to suppress that warning (among others).


--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:400ed65f$1@forums-2-dub...
> In 'true' ANSI NULL behaviour
>
> NULL != 0
>
> and (possibly in your case)
>
> 1 + NULL + 3 = NULL
>
> That warning probably means that
>
> (One+two+Three) IS NULL
>
> [for one or more of your result set rows]
> and those have been eliminated by the
>
> sum( )
>
> aggregate function.
>
> What you probably want to/need to consider doing
> is using the IFNULL( ) function provided something
> like
>
> select sum(ifnull("One",0,"One")+
> ifnull("two",0,"two")+
> ifnull("Three",0,"Three")) as NetSales
> from sales.
>
> Alternatively you can change your schema to default to 0.0
> and not allow nulls into your data.
>
>
> <Chandresh Patel> wrote in message
> news:400ec4fc.4a97.846930886@sybase.com...
> > I am using sybase anywhere 8.02 and after that i start to
> > upgrade all to 9.0 and finally my program give me error like
> >
> > Null value eliminitaed in aggregate function
> >
> > when i am using this query
> >
> > select sum(One+two+Three) as NetSales from sales.
> >
> > Advanced thank you for help.
>
>


Chandresh Patel Posted on 2004-01-21 22:26:03.0Z
Sender: 4ebd.400efc28.1804289383@sybase.com
From: Chandresh Patel
Newsgroups: ianywhere.public.general
Subject: Re: Null value eliminitaed in aggregate function in Ver 9.0
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <400efc7b.4ec1.846930886@sybase.com>
References: <400ec4fc.4a97.846930886@sybase.com><400ed65f$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jan 2004 14:26:03 -0800
X-Trace: forums-1-dub 1074723963 10.22.241.41 (21 Jan 2004 14:26:03 -0800)
X-Original-Trace: 21 Jan 2004 14:26:03 -0800, 10.22.241.41
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2364
Article PK: 4568


> In 'true' ANSI NULL behaviour
>
> NULL != 0
>
> and (possibly in your case)
>
> 1 + NULL + 3 = NULL
>
> That warning probably means that
>
> (One+two+Three) IS NULL
>
> [for one or more of your result set rows]
> and those have been eliminated by the
>
> sum( )
>
> aggregate function.
>
> What you probably want to/need to consider doing
> is using the IFNULL( ) function provided something
> like
>
> select sum(ifnull("One",0,"One")+
> ifnull("two",0,"two")+
> ifnull("Three",0,"Three")) as NetSales
> from sales.
>
> Alternatively you can change your schema to default to 0.0
> and not allow nulls into your data.
>
>
> <Chandresh Patel> wrote in message
> news:400ec4fc.4a97.846930886@sybase.com...
> > I am using sybase anywhere 8.02 and after that i start
> > to upgrade all to 9.0 and finally my program give me
> error like >
> > Null value eliminitaed in aggregate function
> >
> > when i am using this query
> >
> > select sum(One+two+Three) as NetSales from sales.
> >
> > Advanced thank you for help.
>
>

Thank you very much........Thank you Thank you Thank you
Thank you Thank you Thank you Thank you Thank you Thank you
Thank you Thank you Thank you Thank you Thank you


Chris Keating (iAnywhere Solutions) Posted on 2004-01-22 14:33:56.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <400ec4fc.4a97.846930886@sybase.com><400ed65f$1@forums-2-dub> <400efc7b.4ec1.846930886@sybase.com>
Subject: Re: Null value eliminitaed in aggregate function in Ver 9.0
Lines: 87
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <400fe19c$1@forums-2-dub>
X-Original-Trace: 22 Jan 2004 06:43:40 -0800, keating-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Jan 2004 06:17:57 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Jan 2004 06:33:56 -0800
X-Trace: forums-1-dub 1074782036 10.22.108.75 (22 Jan 2004 06:33:56 -0800)
X-Original-Trace: 22 Jan 2004 06:33:56 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2367
Article PK: 4573

An alternative is the "Suppress Fetch Warnings" option in the ODBC dsn.

FYI Since this is a warning, you can safely ignore it and let your
application proceed (provided you are able to ignore these warnings).

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

<Chandresh Patel> wrote in message
news:400efc7b.4ec1.846930886@sybase.com...
> > In 'true' ANSI NULL behaviour
> >
> > NULL != 0
> >
> > and (possibly in your case)
> >
> > 1 + NULL + 3 = NULL
> >
> > That warning probably means that
> >
> > (One+two+Three) IS NULL
> >
> > [for one or more of your result set rows]
> > and those have been eliminated by the
> >
> > sum( )
> >
> > aggregate function.
> >
> > What you probably want to/need to consider doing
> > is using the IFNULL( ) function provided something
> > like
> >
> > select sum(ifnull("One",0,"One")+
> > ifnull("two",0,"two")+
> > ifnull("Three",0,"Three")) as NetSales
> > from sales.
> >
> > Alternatively you can change your schema to default to 0.0
> > and not allow nulls into your data.
> >
> >
> > <Chandresh Patel> wrote in message
> > news:400ec4fc.4a97.846930886@sybase.com...
> > > I am using sybase anywhere 8.02 and after that i start
> > > to upgrade all to 9.0 and finally my program give me
> > error like >
> > > Null value eliminitaed in aggregate function
> > >
> > > when i am using this query
> > >
> > > select sum(One+two+Three) as NetSales from sales.
> > >
> > > Advanced thank you for help.
> >
> >
>
>
> Thank you very much........Thank you Thank you Thank you
> Thank you Thank you Thank you Thank you Thank you Thank you
> Thank you Thank you Thank you Thank you Thank you