Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
NULL and casting |
Mon, Jul 13 2009 10:58 PM | Permanent Link |
Phil Read | Hi All,
Pretty simple question. I have 3 fields in a record I want to SUM in a query, these 3 fields are BCD 2 data type. Now when I run a query to SUM them for example: SELECT transtype, SUM(comm+comm_management+comm_userdefined) AS commission FROM trans GROUP BY transtype I will get a NULL in the commission field if any one of the 3 fields did not contain an amount. Now I have tried casting all 3 separately and tried casting them all together as FLOAT but I still get NULL if any one of the 3 fields I'm adding is NULL. So question is how do I fix it? Thanks guys, Phil |
Mon, Jul 13 2009 11:13 PM | Permanent Link |
"Jeff Cook" | Phil Read wrote:
> Hi All, > > Pretty simple question. > > I have 3 fields in a record I want to SUM in a query, these 3 fields > are BCD 2 data type. > > Now when I run a query to SUM them for example: > > SELECT > transtype, > SUM(comm+comm_management+comm_userdefined) AS commission > FROM trans > GROUP BY transtype > > I will get a NULL in the commission field if any one of the 3 fields > did not contain an amount. Now I have tried casting all 3 separately > and tried casting them all together as FLOAT but I still get NULL if > any one of the 3 fields I'm adding is NULL. > > So question is how do I fix it? > > Thanks guys, > > Phil Kia Orana Phil Read COALESCE -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Tue, Jul 14 2009 1:04 PM | Permanent Link |
"Robert" | "Phil Read" <phil@vizualweb.com> wrote in message news:188637A5-2496-4A1D-9F4F-395AE689C8D3@news.elevatesoft.com... > > I will get a NULL in the commission field if any one of the 3 fields did > not contain an amount. Now I have tried casting all 3 separately and tried > casting them all together as FLOAT but I still get NULL if any one of the > 3 fields I'm adding is NULL. > SUM(if(comm = null then 0.00 else comm)) + same for the others Robert |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |