Hi everyone
I am not able to fix this error. I tried casting as numeric but no luck. What do you suggest?
table
CREATE TABLE [dbo].[Table1](
[UNDERLYING_SYMBOL] [nchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[Ratio_V] [numeric](7, 6) NOT NULL,
[Ratio_O] [numeric](7, 6) NOT NULL,
[Ratio_D] [numeric](7, 6) NOT NULL
) ON [PRIMARY]
GO
query
TRUNCATE TABLE DBO.Table1
INSERT INTO DBO.Table1
SELECTT3.UNDERLYING_SYMBOL,
T3.QUOTE_DATE,
IIF(T4.C_V = 0, 0, 1.0 * T3.P_V / T4.C_V) AS Ratio_V,
IIF(T4.C_O = 0, 0, 1.0 * T3.P_O / T4.C_O) AS Ratio_O,
IIF(T4.C_D = 0, 0, 1.0 * T3.P_D / T4.C_D) AS Ratio_D
FROM ...
error
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
What are the data types (w/ precision & scale) of T3.P_V, T4.C_V, T3.P_O, T4.C_O, T3.P_D, & T4.C_D?
Can you provide a sample script of failing data?
May 2, 2023 at 11:56 pm
Post duplicated
May 3, 2023 at 12:05 am
thank you for that!
The source table had a different level of precision. Once I matched the destination so it matched the source it worked.
May 3, 2023 at 12:07 am
You probably have one or more calculations w/ a result > 100
Contrived example... This succeeds:
This succeeds:
SELECT 'Symbol',
'2023-05-02',
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_V,
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_O,
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_D;
This fails:
SELECT 'Symbol',
'2023-05-02',
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_V,
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_O,
CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_D;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply