January 22, 2020 at 3:46 pm
I have a fact table with two numeric columns defined as DECIMAL(28, 12) and I am attempting to load some of these values into another table where the columns are defined as DECIMAL(15, 2). I was getting an arithmetic overflow error, but none of the values look too large for 15, 2. I tried using ROUND(val, 2) and that made no difference. I then used TRY_CAST on the basis that the problematic values would then come through as null, so I'd be able to check them - nothing failed to cast. If I just use CAST instead, the arithmetic overflow error comes back. The really strange thing is that I used the Redgate "script as insert" option on the data in order to create a temp table to post here, but when I read the temp table (still casting to DECIMAL(15, 2), I don't get the error, so there's not much point in posting it. Can anyone explain why the CAST would fail, but TRY_CAST wouldn't generate a null value?
January 22, 2020 at 3:56 pm
DECIMAL(28,12) is 14 on the left, 12 on the right, DECIMAL(15,2) is 13 on the left 2 on the right.
What do you get from the following snippit? Anything where the decimal place is the 15th character?
;with cte1 as
(
select
charindex('.',convert(varchar(50),<your_decimal_28_12_col_name_here>,0) as LocationOfDP
from
<your_table_name_here>
)
select count(*) from cte1 where LocationOfDP >=15
January 22, 2020 at 4:04 pm
Thanks. The highest value I get for LocationOfDP is 7. If there were too many decimal places, I'd have expected the ROUND to fix it.
January 22, 2020 at 4:19 pm
Are you able to post some sample SQL which would allow us to recreate the issue?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 22, 2020 at 4:32 pm
Unfortunately, when I scripted out the inserts, the select worked on the result, ie. just rendering the result into a grid was sufficient to "fix" the underlying error.
January 22, 2020 at 4:33 pm
Only way I can recreate this issue is with a value greater than or equal to 9999999999999.995 (or the same negative value); which does implies you have a value like that
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 22, 2020 at 4:49 pm
Unfortunately, when I scripted out the inserts, the select worked on the result, ie. just rendering the result into a grid was sufficient to "fix" the underlying error.
Understood, but I thought maybe you would be able to include DDL for a sample of the fact table & then demonstrate the error by selecting / inserting from that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 22, 2020 at 4:53 pm
Phil,
I'll try to reproduce with sample data tomorrow (and post it if I succeed).
Thom,
didn't your large value came through as null when using TRY_CAST? It did when I tried in SSMS.
January 22, 2020 at 4:55 pm
Thom,
didn't your large value came through as null when using TRY_CAST? It did when I tried in SSMS.
It does with TRY_CONVERT, not with CONVERT; which implies you're using CONVERT somewhere on a number that can't be converted (due to an arithmetic overflow), or there's an implicit conversion somewhere. Impossible to know with no data (DDL and DML), and no SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 22, 2020 at 5:03 pm
I tried to script out the values (see below). Selecting from the resulting temporary table works whether I use CAST or TRY_CAST, ie. the value has somehow been fixed by copying it to the temporary table. I'll try to dig deeper tomorrow. Just a thought; is it possible that the CAST is failing on a value that is filtered out later by the WHERE clause (or the INNER JOIN)? This would explain why I don't see a null when using TRY_CAST.
drop table #TempTable;
create table #TempTable (UniqueCode varchar(11), CurrentValue decimal(28, 12));
with Vals as
(select distinct fact_position_latest.current_value_reporting_currency
from dm.dim_cash_account
inner join cpo.stage_perm_cp_client spClient on dim_cash_account.parent_customer_account_code = spClient.EntityCode
inner join dm.fact_position_latest on dim_cash_account.cash_account_id = fact_position_latest.cash_account_id
where dim_cash_account.account_closed_flag = 'N'
and dim_cash_account.cash_account_number <> 'Unknown')
insert into #TempTable (UniqueCode, CurrentValue)
select 'TestCode' + right('000' + cast(row_number() over (order by Vals.current_value_reporting_currency) as varchar(3)), 3),
Vals.current_value_reporting_currency
from Vals
order by Vals.current_value_reporting_currency;
select UniqueCode, cast(CurrentValue as decimal(15, 2))
from #TempTable;
select UniqueCode, try_cast(CurrentValue as decimal(15, 2))
from #TempTable;
January 23, 2020 at 9:31 am
I have confirmed that the error was caused by a value that was being filtered out later by the joins or the where clause. That's why I never saw nulls in the result set when using TRY_CAST.
Thanks to all who responded.
February 10, 2020 at 2:49 pm
Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?
February 10, 2020 at 2:55 pm
Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?
That shouldn't be possible. Per Logical Processing Order of the SELECT statement, the SELECT is processed after the WHERE, JOIN, ON, etc clauses:
In my scenario, the reason was because it was the function's SELECT that was generating the error, which was using data that would have been filtered out elsewhere. When running the SQL outside of the SP, then the order of processing completed the filtering first, where as when the SP itself was run, the filtering happened after, and so the error occured.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 10, 2020 at 4:03 pm
OK. I ask because I have seen it too, despite the logical ordering of processing that I thought was the last word on it.
https://www.sqlservercentral.com/forums/topic/select-processed-before-where-clause#post-3696457
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply