If I have an SQL query which returns an aggregate of several decimal fields
like so:
(sum(COALESCE(m yDecimal1, 0)+
sum(COALESCE(my Decimal2, 0)+
sum(COALESCE(my Decimal3, 0)) as MyTotal
I get an rounded integer in MyTotal.
However, if I do the following:
sum(COALESCE(my Decimal1, 0)+
COALESCE(myDeci mal1, 0)+
COALESCE(myDeci mal1, 0)) as MyTotal
I get a (proper) decimal value.
Does anyone know why the first case returns an Integer?
- Don
4  4731 
On Thu, 28 Sep 2006 20:37:33 GMT, Don wrote:
>If I have an SQL query which returns an aggregate of several decimal fields
like so:
(sum(COALESCE(m yDecimal1, 0)+
sum(COALESCE(my Decimal2, 0)+
sum(COALESCE(my Decimal3, 0)) as MyTotal
I get an rounded integer in MyTotal.
However, if I do the following:
sum(COALESCE(my Decimal1, 0)+
COALESCE(myDeci mal1, 0)+
COALESCE(myDeci mal1, 0)) as MyTotal
I get a (proper) decimal value.
Does anyone know why the first case returns an Integer?
Hi Don,
Can you please post a full repro script? (That is, post CREATE TABLE
statements, INSERT statements and SELECT statements to reproduce this
behaviour in an empty database).
--
Hugo Kornelis, SQL Server MVP
Don wrote:
If I have an SQL query which returns an aggregate of several decimal fields
like so:
(sum(COALESCE(m yDecimal1, 0)+
sum(COALESCE(my Decimal2, 0)+
sum(COALESCE(my Decimal3, 0)) as MyTotal
I get an rounded integer in MyTotal.
However, if I do the following:
sum(COALESCE(my Decimal1, 0)+
COALESCE(myDeci mal1, 0)+
COALESCE(myDeci mal1, 0)) as MyTotal
I get a (proper) decimal value.
Does anyone know why the first case returns an Integer?
What do the following return?
sum(coalesce(my Decimal2,0)) as MyTotal2
sum(coalesce(my Decimal3,0)) as MyTotal2
Sorry, I'm a bit too busy to create more details for this oddity. It's part
of a larger query, so maybe that was affecting something. When I tried
doing to two different summations alone, they both returned decimal values.
It's not big deal. I was just curious as to why it behaved that way. I
must've missed something. No worries.
- Don
I discovered what I had missed. The "scale" property for some of the
decimal fields was set to 0. This caused the COALESCE() function to
truncate the fractions.
- Don
"Don" <un*****@oblivi on.comwrote in message
news:aecTg.6559 4$5R2.15178@pd7 urf3no...
Sorry, I'm a bit too busy to create more details for this oddity. It's
part of a larger query, so maybe that was affecting something. When I
tried doing to two different summations alone, they both returned decimal
values.
It's not big deal. I was just curious as to why it behaved that way. I
must've missed something. No worries.
- Don
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Martin |
last post by:
Hi
I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.
When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first...
|
by: scott |
last post by:
i've come across a real head-hurter. I'm looping through a recordset and
response.writing it's rows out with no problem except 1 field. The field
type is varchar and contains words like meeting, holiday, etc.
Problem is, I'm trying to render a "n/a" when the field is null as in
LISTING 1 below. My code isn't catching the null values. How can I test for
null values? I could swear I've successfully used the Len() test like below...
|
by: John Greve |
last post by:
Any ideas on why my the result 'green' row
column DELTA does not yield -1 for?
That is not how I expected COALESCE( ... ) to work.
Every other row for DELTA (including 'black')
comes out the way I expected.
Thanks for considering this,
John G.
|
by: Stanley Sinclair |
last post by:
Given I have binary input which is to be stored in one table, and it
may consist of any size -- ie, VARCHAR(n) FOR BIT DATA, WHERE THE n is
less than 32K, or the n is bigger such that it is a BLOB ...
Given that I have a LOB table too, named LOBTABLE,
Is there any problem with,
CREATE TABLE(
ID INTEGER NOT NULL,
|
by: Daniel Vallstrom |
last post by:
I'm having problems with inconsistent floating point behavior
resulting in e.g.
assert( x > 0.0 && putchar('\n') && x == 0.0 );
holding. (Actually, my problem is the dual one where I get
failed assertions for assertions that at first thought ought
to hold, but that's not important.)
At the end is a full program containing the above seemingly
| | |
by: Anders Borum |
last post by:
Hello!
Now that generics are introduces with the next version of C#, I was
wondering what kind of performance gains we're going to see, when switching
from e.g. the general hashtable to a hashtable that supports (or implements)
generics?
I haven't tried generics hands-on, but I would assume that once you don't
have to do casting anymore, things could speed up quite a bit.
|
by: Carl |
last post by:
Hi,
I have an array of bytes like
Byte m_Data = new Byte ;
Byte sequences starting at a random index (4 byte aligned) should be
interpreted as unsigned long values and compared with each other.
|
by: Jeff |
last post by:
ASP.NET 2.0
In the business logic layer I've got the code (see CODE) below, this code
gives a compile error (see ERROR)
CODE:
List<Messagemessages = null;
messages = HttpContext.Current.Session;
ERROR:
|
by: eshanne |
last post by:
<?php get_header();?>
<!-- begin col left -->
<div id="colLeft">
<?php if (have_posts()) : while (have_posts()) : the_post(); ?>
<!-- begin post -->
<div class="blogPost clearfix">
<h1><a href="<?php the_permalink() ?>"><?php the_title(); ?></a></h1>
<div class="meta">
<img src="<?php bloginfo('template_directory'); ?>/images/ico_file.png" alt="Posted" /> Posted by <?php the_author(); ?> in <?php the_category(', ')...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
