August 11, 2010 at 9:35 am
Can someone explain this strange behavior to me?
When I run this query:
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL
DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
DROP TABLE #Table2
CREATE TABLE #Table1
(
Col1 DECIMAL,
Col2 DECIMAL
)
CREATE TABLE #Table2
(
Col1 DECIMAL,
Col2 DECIMAL,
Col3 DECIMAL
)
DECLARE @i INT
SET @i = 45
WHILE @i <= 135
BEGIN
INSERT INTO #Table1 ( Col1, Col2 )
VALUES ( /* Col1 - DECIMAL */ @i,
/* Col2 - DECIMAL */ 100 )
SET @i = @i + 45
END
SELECT *,
Col1 / Col2 AS 'Result'
FROM #Table1
INSERT INTO #Table2 ( Col1, Col2, Col3 )
SELECT *,
Col1 / Col2 AS 'Result'
FROM #Table1
SELECT *
FROM #Table2
DROP TABLE #Table1
DROP TABLE #Table2
I get the following result:
#Table1:
Col1Col2Result
451000.4500000000000000000
901000.9000000000000000000
1351001.3500000000000000000
#Table2:
Col1Col2Col3
451000
901001
1351001
Inserting into #Table2 with a subquery causes the computed column to be rounded off to the nearest whole number.
If I run the same query with a "SELECT...INTO #Table2", the results come out correctly. So I guess I've found the workaround, but I can't make heads or tails of this behavior. Anyone have an idea?
August 11, 2010 at 9:41 am
Chi Chi Cabron (8/11/2010)
Can someone explain this strange behavior to me?Inserting into #Table2 with a subquery causes the computed column to be rounded off to the nearest whole number.
If I run the same query with a "SELECT...INTO #Table2", the results come out correctly. So I guess I've found the workaround, but I can't make heads or tails of this behavior. Anyone have an idea?
I imagine the problem is that you haven't explicitly set the size of the decimal datatype in either table.
August 11, 2010 at 10:06 am
That did it, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply