I'm writing a query which do some calculations over some tables to return to my application, after debugging I've found out that the undesired truncation is being done directly in SQL Server instead of my app.
I've got those two tables and columns (considering both valued and pop_valued are decimal(38,9)):
select valued
from dbname.dbo.TABLE_SUM
where market_id_2 = 103 -- returns 2454356.000000000
select pop_valued
from dbname.dbo.TABLE_POP
where market_id_1 = 103 -- 8035229.000000000
And the query that does the calculation is:
SELECT
sm.totalsortkey_bigid_0 AS totalsortkey_bigid_0,
COALESCE(sm.market_id_2, 0) AS market_id_2,
sm.advertiser_id_3 AS advertiser_id_3,
sm.time_day_4 AS time_day_4,
SUM(100.0 * (sm.valued /
NULLIF(b.pop_valued, 0))) AS valued
FROM
dbname.dbo.TABLE_SUM sm
JOIN
dbname.dbo.TABLE_POP b ON sm.market_id_2 = b.market_id_1
AND sm.population_id_1 = b.population_id_0
GROUP BY
sm.totalsortkey_bigid_0,
sm.market_id_2,
sm.advertiser_id_3,
sm.time_day_4
where, for market_id = 103 the 'valued' is displayed as: 30.544900
But if I do this test query with the same values:
select sum(100 * (2454356.000000000 / NULLIF(8035229.000000000, 0)))
then I get the desired result which is: 30.544941531846821043.
I've found some similar questions like this one and tried to apply the cast(val as decimal(38,9)) but it didn't worked. So my question is how to avoid this truncate behavior on the query to return the desired calculation?