Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Total currency fields in the same record when some are NULL |
Thu, Feb 21 2008 6:06 PM | Permanent Link |
Phil Read | Hi Guys & Gals,
Real simple, I have a record that has 7 currency fields in it and I'm trying to run a query to calculate the total, the following query works well: SELECT ref, date, tenants, property, (amount1 + amount2 + amount3 + amount4 + amount5 + amount6 + amount7) AS total FROM receipt Only trouble is, if any of the 7 amount fields are NULL then it doesn't return the total, it's just blank. Is there an easy way to make this query calculate NULL as 0.00 on those currency fields without changing the table so all amount fields are 0.00 if they are not set? Thanks, Phil. |
Thu, Feb 21 2008 7:06 PM | Permanent Link |
"Robert" | "Phil Read" <phil@vizualweb.com> wrote in message news:E2D63557-CFFF-4411-8016-9104AB0A0BD1@news.elevatesoft.com... > > Only trouble is, if any of the 7 amount fields are NULL then it doesn't > return the total, it's just blank. > SELECT IF(F1 = NULL THEN CAST(0.00 AS DECIMAL) ELSE F1) + IF(F2 = NULL THEN CAST(0.00 AS DECIMAL) ELSE F2) AS TOTAL FROM TABLE Robert |
Fri, Feb 22 2008 2:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
Wrap each of the fields in COALESE (or however you spell it) in the sql Roy Lambert |
Fri, Feb 22 2008 11:48 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:1C33459C-64BA-4C01-97F0-F74D88CCA313@news.elevatesoft.com... > Phil > > Wrap each of the fields in COALESE (or however you spell it) in the sql > How would you use COALESCE for this query? Robert |
Fri, Feb 22 2008 12:58 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
select (COALESCE(amount1,0) + COALESCE(amount2,0)) As Maxed from params Roy Lambert |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |