How Can I Show Decimals After Division Update
Jul 20, 2005
I have a table which I want to update by dividing one field into
another. The update runs with no errors, but the results come out as
only a positive integer number.
The datatype for the result field is float.
The datatype for the other fields are int.
I have tried testing by dividing 7 by 10000, which should give me
0.0007. The result in the database is 0.
How do I define a field to show the full value with decimal positions?
If I enter .0007 into the field through the Enterprise Manager, it shows
0007. When I update by dividing, it shows 0.
Any help would be welcome.
Joel
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
View 15 Replies
ADVERTISEMENT
Oct 25, 2000
I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???
View 2 Replies
View Related
Feb 22, 2004
Hi all,
This is what I have:
DECLARE @OnTime int
DECLARE @UnControlled int
DECLARE @Volume int
DECLARE @GrossEffect decimal(10,2)
DECLARE @NetEffect decimal(10,2)
DECLARE @WeekEndDate datetime
SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
SET @GrossEffect = (@OnTime/@Volume * 100)
SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)
INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
VALUES ('AN',
@Volume,
@NetEffect,
@GrossEffect,
@WeekEndDate)
My numbers are:
OnTime=8089
Uncontrolled=6
Volume=8095
The GrossEffect comes as 0 and the NetEffect=100
AN809501002/21/2004
Why is my GrossEffect = 0??? it should be 99.9
View 1 Replies
View Related
Feb 19, 2008
Hello, I have a table with a field with the format of 8 decimals. When I make a select to that table instead is given me (for example) 25,00 it gaves me 25,00000000. How can I fix the with the correct format? Thanks in advance.
View 2 Replies
View Related
Jul 7, 2005
I need to display decimal results from SQL Server queries, but the decimals keep getting truncated. For example, 9 divided by 4, keeps getting truncated to 2. Does anyone know how to display the whole result, including the decimals?
View 2 Replies
View Related
Aug 17, 2005
I have a field in my SQL table that is defined Decimal(10,8), which
provides me with the ability to have up to 8 digits after the decimal
place, however, if I store a value such as 3.14, it is stored as
3.14000000. This is fine in the database, but it is the same when it's
returned. I have tried using Convert.ToDecimal on my returned row but
it doesn't work. The value at runtime appears as 3.14D in the locals
window but displays with all the insignifcant digits.
Any ideas what's happening or if there is an easy way to trim the trailing zeros?
View 2 Replies
View Related
Aug 24, 2004
Hello,
I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))
181.25
176.5
170
I want the view to show
181.25
176.50
170.00
I have tried Cast but that doesn't seem to do the job.
Help!
Rolf
View 1 Replies
View Related
Jun 24, 2008
in quantity column - i have
100.00000
but i want only 100
no decimals..
how to get that?
thanks
View 2 Replies
View Related
May 31, 2007
I have two fields whose format in the table is decimal (14,5).
I now have to divide one field by the other, and I want the result
to be in the format decimal (21,16).
Whatever I do I'm not able to get this result with sixteen decimals.
I tried with dec(Field1,21,16)/Field2 but it yields six decimals.
I tried with dec(Field1,21,16)/Field2 but it yields an integer.
I tried other ways but I always get a result which is made of maximum
six decimals.
What can I do?
Thank you.
Anna - Verona (Italy)
View 5 Replies
View Related
Jan 16, 2006
I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?
View 6 Replies
View Related
Feb 27, 2004
Hi,
Can anyone tell me why this doesn't return any decimals?
declare @f float
set @f = 6 / 18
print @f
All i get is 0... Is there something obvious i've done wrong?
TIA
View 3 Replies
View Related
Aug 9, 2000
Hi All
I am trying to devide 24 by 30 result it gives 0, but I wouldlike to get 0.8
how can I achieve this
I will apprecite your help, thank you
View 1 Replies
View Related
Mar 5, 2003
Hi all,
I have a prob with the following code...
in that the value of the amount of the year 2001
is 0 so it is showing me division by zero prob
can any one change the code and let me know the details
declare @year1 int
declare @year2 int
declare @month int
set @year1 = 2002
set @year2 = 2001
set @month =9
select
case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0
else
(
(sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) -
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end)
)* 100 end as Percentage,
from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site
Thanks
View 1 Replies
View Related
May 1, 2007
I'm very new to sql server and still don't know the in's and out's I have this:
Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END)/ Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Per_Quoted
I keep on getting 0, anybody have any ideas?
View 6 Replies
View Related
Feb 23, 2001
I'm using the sum() agregate function inside a select with a decimal field with an scale of 2.
I want to get the result always with 2 decimals, but if the result is 100.10 I get 100.1 . I've tried
to convert and cast the result but I can't find how to get 2 decimals. Please help. Thanks
View 1 Replies
View Related
Aug 18, 2004
Hello,
I have encountered a problem doing an update on a Field defined as DECIMAL with dbcursor().
I have opened a cursor with dbcursoropen() with option LOCK_CC set. Then I have bound some char variables to CHAR Fields with STRINGBIND and some double Variables to the DECIMAL fields with FLT8BIND.
When I read the cursor, the data is correct in the bound variables. But when I do an update, all data for the numeric fields are gone and the database contains only NULL Values. The Strings are updated correctly.
I have tried to set the length, I am using the poutlen-variables with a value other than 0, but nothing helps.
Does anyone have an idea about what can I do?
Thank You in advance.
Frank
View 3 Replies
View Related
May 18, 2008
In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.
Thanks for the info.
View 1 Replies
View Related
May 19, 2008
How can i add two trailing decimals if there is no decimals:
for example if it is just 1, then make it 1.00
if it is 1.12 then leave it as it is.
can you please help.
select cast(ordernumber as varchar(10)) + '00' from ordertable
Thanks for the info.
View 1 Replies
View Related
May 22, 2008
Hello
I need a help ..
I need to convert 45.4593251 to 45.46
How to achieve it.
Can any one help me please..
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
View 3 Replies
View Related
Nov 10, 2005
I want to save a decimal in my sql table. It's an hourly rate. I have my field as smallmoney type. I am updating the table using a sp from my web page. It always rounds my entry up eg 3.75 becomes 4.00. Heres my sp and the line where I am passing the parameter to the sp.
CREATE PROCEDURE spRB_AmendRoomData
@strRoomId int,
@strRoomRef nvarchar (50),
@strFloor nvarchar (50),
@strZone nvarchar(10),
@strLocation nvarchar (100),
@strType nvarchar(50),
@strNo int,
@strDirections nvarchar(2000),
@strPhoneNo nvarchar (50),
@strMaxNo int,
@strEquipmentFac nvarchar(2000),
@strNotes nvarchar(2000),
@strCharge smallmoney,
@strNotInUse bit,
@strIntExt nvarchar(20)
AS
BEGIN
UPDATE tblRB_Rooms SET
RM_RoomRef=@strRoomRef,
RM_Floor=@strFloor,
RM_Zone=@strZone,
RM_Location=@strLocation,
RM_Type=@strType,
RM_No=@strNo,
RM_Directions=@strDirections,
RM_PhoneNo=@strPhoneNo,
RM_MaxNo=@strMaxNo,
RM_Equipment_Facilities=@strEquipmentFac,
RM_Notes=@strNotes,
RM_Charge=@strCharge,
RM_NotInUse=@strNotInUse,
RM_IntExt=@strIntExt
WHERE
RM_RoomId = @strRoomid
END
GO
Cmd.Parameters.Add(New SqlParameter("@strCharge", Me.txtCharge.Text))
Thanks for any help
View 4 Replies
View Related
May 14, 2008
Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.
(a bug forgot to round)
Thanks for your help
View 4 Replies
View Related
Mar 27, 2007
I have the following field(Fields!SequenceNO.Value), coming form database.
i would like to have an expression IIF...
if the value coming from database is just 1, then make it 1.00 or
if the value coming from database has a period or point say 1.01 then show as it is.
thank you very much for the help / information....
View 1 Replies
View Related
May 18, 2015
Script as follows :
select cast( 0.0050000000 as decimal(38,23)) * cast(0.0000010000 as decimal(38,23))
select cast( 0.0050000000 as decimal(28,23)) * cast(0.0000010000 as decimal(28,23))
select cast( 0.01 as decimal(28,15)) * cast(0.0000000001 as decimal(28,15))
select cast( 0.01 as decimal(28,16)) * cast(0.0000000001 as decimal(28,16))
The result was following：
0.0000000--was zero
0.000000005000000000000000000
0.00000000000--was zero
0.0000000000010
View 3 Replies
View Related
Apr 15, 2008
Hi,
Im having a decimal value of 1.24
im rounding off the value to 1 precision
ex:- select round(1.24,1)
the output its returning is 1.20
i want to get only 1.2
can anyone suggest on this??
View 3 Replies
View Related
Dec 17, 2007
In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X).
The problem is that X can be 0 sometimes and then I get an error.
How can I prevent errors like this for the case of X=0?
In access I would use IIF function, but it doesn't appear in SQL SERVER views.
Thanks.
View 5 Replies
View Related
Mar 7, 2005
Hi,
I'm trying to divide two decimal(19,4) numbers but it keeps giving me a Divide by 0 error.
- 10698.25 / 76782.11 = -0.13 (but I get a Divide by 0)
I looked it up in the Sql Server docs, does the Divide by 0 error also cover Stack overflows?
If so, how do I get around this?
Many thanks,
Stuart
View 1 Replies
View Related
Jul 31, 2007
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
View 14 Replies
View Related
Mar 7, 2008
hi
in a view, i create a field like field1/field2.
in case that field2 is zero, i've got division by zero error.
how can i change the value to 0 when field2 is 0 for avoiding get error.
View 1 Replies
View Related
Nov 20, 2007
I want to count the rows in two tables and then give a percentage as a result.
Something like:
SELECT Count(*) / (SELECT COUNT (*) FROM Table2) FROM Table1
Just not quite sure how to do this.
*Thanks*
View 14 Replies
View Related
Dec 11, 2007
It is common for me to need to create ratios from data in my database such as
SELECT
( list_value / sale_price ) as ratio
FROM
values
The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?
doco
View 5 Replies
View Related
Dec 14, 2007
Hi,
I have a table in which i have two colums say discription and counts.
the table has 10 rows.
This table is created by extracting data from other table means its not a table that exist in system.
Now in my last row i want discription as '%mailed' and count as row1/row2
can u tell me how to do that?
View 2 Replies
View Related
Mar 15, 2008
SELECT [Record Number], Date, Car, Miles_Start, Miles_Stop, Gallons_FillUp, Miles_Stop - Miles_Start AS Miles_Total, Miles_Total / Gallons_FillUp AS MPG
FROM Sheet1_Table
ORDER BY Date DESC, Car
Hi,
I have Visual Studio 2008 Pro / Using VB 9.0 / SQL Server Express 2005. I am making a Miles per gallon calculator for my dad. All Fields /Cells are populated in two records excepting the Miles_Total & MPG. The Miles_Total Comes out fine but... the MPG does not . Any help with this Query would be greatly appreciated!
Jeff L
View 3 Replies
View Related
Nov 15, 2007
I've got the following SQLSELECT count(*) FROM tablea AJOIN tableb B ON ..etc..WHERE a.string_val = 'test' ANDb.divider 0 ANDa.number 0 AND(a.number / b.divider 0)The b.divider value can be 0, but still I get the division by zeroerror message.I guess the reason is that the database performs the where statementsone at the time?So when performing the division a.number / b.divider it could get 0 inthe divider even if b.divider 0 is also part of the wherestatement??My question is then.. How can I work around this problem? Changing thedatabase to set b.divider always 0 is not an option
View 2 Replies
View Related
