asked
Please advise on Arithmetic overflow error converting varchar to data type numeric
i need to run a query to select specific set of raws (SAC) with year month and FUND type and kept getting below error. Here is my query.. SELECT * FROM [dbo].[disb_details] WHERE year = 2016 and Month = 01 and FUND = 'FHCS' and SAC= '109002 129002 159014 159015 179010 199004 199009 199014 209001 209002 209005 209009 209012 219013 219903 229004 229011 229013 229014 239003 239004 239005 239006 239023 249001 249002 249004 259002 259004 259005 259010 259025 259029 259908 269001 269004 269006 269007 269008 269009 269024 269905 279009 279010 279012 279041 279046 289001 289002 289010 299001 299008 299010 299905 319010 319019 319026 339006 339007 339010 339011 339012 339014 339015 339016 339017 339023 339024 339920 349007 349008 349009 349011 359001 359008 359010 359011 359016 359022 359027 359028 359029 359030 359031 359033 359034 359036 359037 359038 359039 359041 359043 359044 359045 359046 359047 359053 359054 359059 359070 359071 359075 359081 359082 359083 359084 359086 359089 359090 359091 359093 359094 359098 359100 359101 359102 359107 359109 359111 359112 359113 359114 359117 359118 359121 359122 359131 359132 369001 369002 369004 369014 379008 379010 379013 379019 389005 389006 389007 389008 389009 389010 389015 399003 399009 399014 399016 399018 409003 409004 419007 419008 419009 419010 419011 419012 419014 419015 419016 419020 419905 429002 429007 429789 429790 439003 439004 439008 439012 439013 439017 439019 439026 439031 439035 439039 449001 449002 449003 449004 449006 449015 449018 449019 449020 449022 449026 449030 449040 449043 449046 449052 459001 459002 469001 479009 479012 479013 489007 489009 489011 499001 499002 499006 499007 499009 499010 499011 509002 519001 519005 519905 529001 529003 529004 529013 529910 539002 539004 539006 539007 539010 559007 629002 629003 659001 659002'; ***Msg 8115, Level 16, State 6, Line 1 Arithmetic overflow error converting varchar to data type numeric.*** Please advise
If the SAC column is numerical (which I'm assuming it is) SQL Server can't convert that long string into a viable numeric data type in order to do the comparison. You might want to try something like this: ... FUND='FHCS' and SAC IN (109002,129002,159014,159015,179010,199004,199009,199014,209001,209002,209005,209009,209012,219013,219903,229004,229011,229013,229014,239003,239004,239005 ,239006,239023,249001,249002,249004,259002,259004,259005,259010,259025,259029,259908,269001,269004,269006,269007,269008,269009,269024,269905,279009,279010, 279012,279041,279046,289001,289002,289010,299001,299008,299010,299905,319010,319019,319026,339006,339007,339010,339011,339012,339014,339015,339016,339017, 339023,339024,339920,349007,349008,349009,349011,359001,359008,359010,359011,359016,359022,359027,359028,359029,359030,359031,359033,359034,359036,359037, 359038,359039,359041,359043,359044,359045,359046,359047,359053,359054,359059,359070,359071,359075,359081,359082,359083,359084,359086,359089,359090,359091, 359093,359094,359098,359100,359101,359102,359107,359109,359111,359112,359113,359114,359117,359118,359121,359122,359131,359132,369001,369002,369004,369014, 379008,379010,379013,379019,389005,389006,389007,389008,389009,389010,389015,399003,399009,399014,399016,399018,409003,409004,419007,419008,419009,419010, 419011,419012,419014,419015,419016,419020,419905,429002,429007,429789,429790,439003,439004,439008,439012,439013,439017,439019,439026,439031,439035,439039, 449001,449002,449003,449004,449006,449015,449018,449019,449020,449022,449026,449030,449040,449043,449046,449052,459001,459002,469001,479009,479012,479013, 489007,489009,489011,499001,499002,499006,499007,499009,499010,499011,509002,519001,519005,519905,529001,529003,529004,529013,529910,539002,539004,539006, 539007,539010,559007,629002,629003,659001) Hope that helps!
18 People are following this question.
