Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 14th, 2007, 04:57 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Decimal field SUM rounding up!

I'm using the following~

SELECT @TotalCost = Sum(ItemPrice)
FROM dbo.Orders
WHERE UserID = @UserId

My ItemPrice Column and @TotalCost variable is Decimal(6,2).
But when i call the @TotalCost variable it has been rounded up!

How do i get 2 decimal places on my @TotalCost SUM variable?

 
Old May 14th, 2007, 05:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

It can't be... I think you are missing something there.
Can you post your complete code... that has the Variable declaration too? Also generate the CREATE TABLE script for that table and post it here.

_________________________
- Vijay G
Strive for Perfection
 
Old May 14th, 2007, 06:11 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi happygv,

Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[getOrderData]

@ApplicationName        nvarchar(256),
@UserName                nvarchar(256),
@TotalItemPoints        smallint OUTPUT,
@TotalCost                decimal(6,2) OUTPUT

as 

BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications 
            WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT        ItemID, ItemQTY, ItemPrice, ItemPoints, 
                UserID, OrderDate, CartDesc
    FROM        dbo.Orders INNER JOIN dbo.Products ON 
                dbo.Orders.ItemID = dbo.Products.PID
    WHERE       UserID = @UserId

-- Find Total Points & Cost
    SELECT @TotalItemPoints = Sum(ItemPoints), @TotalCost = Sum(ItemPrice)
    FROM dbo.Orders
    WHERE UserID = @UserId
    IF @TotalItemPoints is NULL
    SET @TotalItemPoints = 0    
--

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate= GetDate()
        WHERE  UserId = @UserId
    END
END
------------------------------------------------------------
Not sure how to generate a create table script but I should tell you I am pulling the data up using ASP.net by the following code:

Code:
Sub CartInserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles Cart_DS.Inserted
                TotalPoints_lbl.Text = "Points: " & e.Command.Parameters("@TotalItemPoints").Value
        TotalCost_lbl.Text = "Total Cost: " & String.Format("{0:N}", Math.Round(CDec(e.Command.Parameters("@TotalCost").Value), 2))

    End Sub
Code:
<InsertParameters>
                            <asp:Parameter Name="TotalCost" Type="Decimal" Direction="Output"
                            ConvertEmptyStringToNull="true" DefaultValue="0.00" />

                        </InsertParameters>


 
Old May 14th, 2007, 07:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You are doing a
Code:
Math.Round(CDec(e.Command.Parameters("@TotalCost").Value),
Is that the culprit?

Moreover should you not use a FIXED POINT rather than using a NUMBER?
String.Format("{0:N}",
replaced with...
String.Format("{1:F}",

I think one of these or combination of these are killing your value.

_________________________
- Vijay G
Strive for Perfection
 
Old May 14th, 2007, 07:49 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the suggestions, the format and rounding I applied to the result was a bit of a red herring as without any formatting it still produced a rounded up figure. I left the code in as it seemed the problem lay elsewhere - I was thinking the SQL. Anyway saying that I tried your ideas - removed the "math.round" and changed to "string.format{1:f}" and they still yield a rounded up figure.
I'm stumped!

 
Old May 14th, 2007, 07:59 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'll just give the results of the changes:
If:

ItemPrice1 = £5.41
ItemPrice2 = £1.10
Actual SUM value should be 6.51, but..
results:
e.Command.Parameters("@TotalCost").Value = 7
String.Format("{0:N}", Math.Round(CDec(e.Command.Parameters("@TotalCost") .Value), 2)) = 7.00
String.Format("{0:F}", CDec(e.Command.Parameters("@TotalCost").Value)) = 7.00
String.Format("{0:F}", e.Command.Parameters("@TotalCost").Value) = 7.00



 
Old May 14th, 2007, 08:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I dont suspect it to be issue with SQL. Since I created a new table with COL1 as decimal(6,2)

1) Did a Select Sum(COL1) from Table1. that resulted only in decimal value.
2) DECLARE @totalcost DECIMAL(6,2)
    SELECT @totalcost = Sum(COL1) from Table1
    SELECT @totalcost
resulted in decimal value. So thought it could be something to do with your frontend code.
See if you are missing something else that we haven't seen yet from the frontend.

Also see if this helps.
String.Format method

cheers

_________________________
- Vijay G
Strive for Perfection
 
Old May 14th, 2007, 09:37 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've been shaking my code about but I can't solve the problem!
I've tried various String Formats; altered the code that interacts with the SQL; tried various controls to display the data- nothing!
When I use a datagrid to show the ItemPrices it displays them correctly with decimal places, so they are OK.
I'm thinking I could use a datatype of Double instead of Decimal as maybe my ASP.net coding would prefer that!
I'm going ot try different data types.

 
Old May 14th, 2007, 09:46 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK i've finally got it!

I changed the ItemPrice DB Type and @ToalItemPrice to 'Money', and in my front end code I set the @TotalItemPrice as a 'Double'.
I then apply String.Format{0:N} to give a consistent 2 Decimal places.
({1:F} doesn't work for me)

thanks for your help happygv ;)
 
Old May 14th, 2007, 10:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hmmmm! that is good to know. It didn't strike my mind to ask what datatype you have been using in front end. That could have saved sometime.

cheers

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
decimal data type all 0's after decimal gobotsoup SQL Server ASP 1 February 28th, 2007 02:32 PM
Help: Running Sum (or Cumulative Sum) timdasa VB Databases Basics 1 August 22nd, 2006 03:12 PM
How to get Cumulative Sum of a Field badsy Crystal Reports 1 January 20th, 2006 05:09 PM
Placing a computed sum in a table field cuzintone SQL Language 1 April 15th, 2005 05:02 AM
Rounding in C# cjo ASP.NET 1.0 and 1.1 Basics 3 November 3rd, 2003 04:12 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.