-2

Someone suggested that the reason I get an error

Arithmetic overflow error converting nvarchar to data type numeric

when running some code is because I need to convert the INT data type of one of my variables to a BIGINT data type.

Does anyone know how to do this? I've tried the code below, but it didn't work!

TIA

    select
    es.ClientProductID,
    es.ProductName,
    ash.sales,
    CAST(ash.sales AS BIGINT) * CAST(ash.price as BIGINT) AS CashSales,
    ash.price,
    ash.cost,
    ash.date
from AggregatedSalesHistory as ash
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID
join PriceZone as pz on pz.PriceZoneID = ash.PriceZoneID 

where 
    es.DepartmentName = 'Dairy' 
    and ash.segmentid = 0
    and pz.Name = 'South' 
    and ash.date >= '2014-1-1' and ash.Date<='2014-12-24'
    and (es.ClientProductID = 7119508806 
    or es.ClientProductID = 7119502372 
    or es.ClientProductID = 7003836538 
    or es.ClientProductID = 7119502437
    or es.ClientProductID = 2500002694
    or es.ClientProductID = 2500002606
    or es.ClientProductID = 7003836540
    or es.ClientProductID = 2500005433
    or es.ClientProductID = 2500005542
    or es.ClientProductID = 2500002893);
Mr 2017
  • 103
  • 2
  • 6
  • 15
  • Do you realize the question you linked is for the language JAVA and not SQL? You instead should look for a SQL version, probably [Cast or Convert](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017). – Erik Philips Sep 14 '18 at 15:17
  • Look up how to convert values in SQL Server. Why would you put Java syntax into SQL Server and expect it to work? – dfundako Sep 14 '18 at 15:18
  • 1
    Oh, ok! Thanks for clarifying that, Erik! That's much appreciated! I'm a newbie to SQL! – Mr 2017 Sep 14 '18 at 15:22
  • is the downvoting just because he failed to get the SQL tags correct? – Cato Sep 14 '18 at 15:42
  • Perhaps the downvoting is due to the duplication from his last question and the omission of important information - such as es.ClientProductID is nvarchar. Since OP is providing numeric literals in the where clause, that forces a conversion of the column. – SMor Sep 14 '18 at 19:54

2 Answers2

0

Multiplying two ints will result in an int, at which point your result will already overflow. You should perform the casting before the multiplication:

CAST(ash.sales AS BIGINT) * CAST(ash.price as BIGINT) AS CashSales
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Hi Mureinik, thanks for the prompt response! I added that line of code, but still got an overflow? I'll update the question with the latest code. Thanks for your help, so far. – Mr 2017 Sep 14 '18 at 16:24
0

You're missing a left paren.

CAST(ash.sales * ash.price) as BIGINT) AS CashSales, should be

CAST((ash.sales * ash.price) as BIGINT) AS CashSales, 

Arithmetic overflow error converting nvarchar to data type numeric

Also, one of your fields is not numeric. You didn't post the structure so I can't tell which one.

Terry Carmen
  • 3,720
  • 1
  • 16
  • 32
  • Hi Terry, thanks for the prompt response and that was a good spot. I added the additional bracket, but still got an error message that said "incorrect syntax near ash" – Mr 2017 Sep 14 '18 at 16:23