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!
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???
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)
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.
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?
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?
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)
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?
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
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
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.
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.
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.
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
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??
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.
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
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.
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?
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
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
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
