-1

I'd like to return results (units, cash, price, cost, etc) for a specific group of products (the top 10 products in a specific Department).

The code I've written below lists the top 10 at the end of the WHERE clause but doesn't return results - it just returns the column titles, but no data??

If I remove the "N's" and inverted commas from the es.ClientProductID numbers at the end (the top 10) then I get this error message: "Arithmetic overflow error converting nvarchar to data type numeric." (es.ClientProductID is an nvarchar data type).

Sales is an INT data type and Price is a DECIMAL data type.

Does anyone know how to resolve this, please? If so, could you please amend the code below? Thanks in advance. 

    select
    es.ClientProductID,
    es.ProductName,
    ash.sales,
    ash.sales * ash.price 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 IN (N'7119508806', N'7119502372', N'7003836538', N'7119502437', N'2500002694', N'2500002606', N'7003836540', N'2500005433', N'2500005542', N'2500002893')
Mr 2017
  • 103
  • 2
  • 6
  • 15
  • A column cannot match multiple values at once. Did you mean `es.ClientProductID = 7119508806 or es.ClientProductID = 7119502372 ...` – Salman A Sep 14 '18 at 12:26

3 Answers3

2

A product can't have two IDs at the same time. You should use an or operator between all the product IDs. Or better yet, use the in operator that's a shorthand for a series of ors:

and es.ClientProductID IN (7119508806, 7119502372, 7003836538, 7119502437, 2500002694, 2500002606, 7003836540, 2500005433, 2500005542, 2500002893)

EDIT:

According to the comments ClientProductID is an nvarchar column, so you should use nvarchar literals, not integral litirals in the in's argument list:

and es.ClientProductID IN (N'7119508806', N'7119502372', N'7003836538', N'7119502437', N'2500002694', N'2500002606', N'7003836540', N'2500005433', N'2500005542', N'2500002893')
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Hi Mureinik, thanks for the prompt response! When I tried that, however, I got an error message that said 'Arithmetic overflow error converting nvarchar to data type numeric.' Do you know how I can resolve it? TIA. – Mr 2017 Sep 14 '18 at 12:40
  • @Mr2017 what type is `ClientProductID`? – Mureinik Sep 15 '18 at 09:45
  • ClientProductID is an nvarchar data type. – Mr 2017 Sep 17 '18 at 08:55
  • @Mr2017 in that case, I did not phrase the condition properly. See my last edit that should fix that issue. – Mureinik Sep 17 '18 at 09:14
  • I edited the code (I've pasted the latest version in the question), but the query still returns no data? Only the column titles. Any other ideas? Thanks in advance. – Mr 2017 Sep 17 '18 at 12:02
  • Hi Mureinik, this query is now resolved. The issue was leading zeros. When I'd originally exported the Top Ten SKUs to Excel and saved them, Excel had removed two leading zeros for each record. So when I'd gone back to SSMS to extract data for the Top Ten SKUs, using the list I had from Excel, the query didn't work because the SKUS didn't have the leading zeros. I didn't know that they'd impact the running of the code, but I've learnt something from the answers on this post (which I incorporated into my code), so thank you! – Mr 2017 Sep 18 '18 at 16:41
2

You can use IN instead. This will check the value of the ClientProductID against all of the products inside the brackets, and return each one if the query matches it against one of the values.

Extract below:

and es.ClientProductID IN (7119508806, 7119502372, 7003836538, 7119502437, 
2500002694, 2500002606, 7003836540, 2500005433, 2500005542, 2500002893)
DKyleo
  • 806
  • 8
  • 11
  • Hi DKyleo, thanks for the prompt response! I modified the query to look like the one above and there were no red lines when I modified it (good sign), but when I ran it, it gave me an error message saying "Arithmetic overflow error converting nvarchar to data type numeric." Is this because there could be too much data? – Mr 2017 Sep 14 '18 at 12:48
  • I would have a check of the data types in your table - if your ClientProductID or your segmentID are nvarchar rather than numeric, it will attempt to convert it - but that doesn't always work. In that instance, you may be better altering the data type in your column to numeric or wrap each ID in ' marks so it doesn't attempt a conversion. – DKyleo Sep 14 '18 at 12:55
  • 1
    If `es.ClientProductID` is a nvarchar then add quotes around the numbers in the above example. – Salman A Sep 14 '18 at 12:56
  • @Mr2017 let us know the datatype of `ash.sales and ash.price`. Because, here Arithmetic operation has been performed. – Pugal Sep 14 '18 at 13:39
  • Hi Salman A, es.ClientProductID is an nvarchar, as you assumed correctly. However, when I ran the code with quotes arouund the Client Product IDs, the query didn't actually return anything. Any other thoughts? I've updated the question. TIA – Mr 2017 Sep 14 '18 at 14:40
  • Hi IdontKnowEnglish, the data types for ash.sales and ash.price are INT and DECIMAL, respectively. I've updated the question. Looking for to hearing about any other thoughts you have? TIA – Mr 2017 Sep 14 '18 at 14:41
  • Hi DKyleo, this query is now resolved. The issue was leading zeros. When I'd originally exported the Top Ten SKUs to Excel and saved them, Excel had removed two leading zeros for each record. So when I'd gone back to SSMS to extract data for the Top Ten SKUs, using the list I had from Excel, the query didn't work because the SKUS didn't have the leading zeros. I didn't know that they'd impact the running of the code, but I've learnt something from the answers on this post (which I incorporated into my code), so thank you! – Mr 2017 Sep 18 '18 at 16:41
1

you are almost close, 

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)

and condition says that, ClientProductID is must have these 10 ids at a time. Whereas or defines that any one of these ids is equals to ClientProductID at a time. Using of (), so all cases are considered as a Unit of success.

Pugal
  • 539
  • 5
  • 20
  • Hi, thanks for the prompt response! I modified the query to look like the one above and there were no red lines when I modified it (good sign), but when I ran it, it gave me an error message saying "Arithmetic overflow error converting nvarchar to data type numeric." Is this because there could be too much data? – Mr 2017 Sep 14 '18 at 12:45
  • Kindly update the question with what you tried? which helps us – Pugal Sep 14 '18 at 13:22
  • Done - I've posted a comment above in response to your questions. I'd be grateful if you could get back to me with any thoughts? TIA. – Mr 2017 Sep 14 '18 at 14:42
  • Hi IdontKnowEnglish, this query is now resolved. The issue was leading zeros. When I'd originally exported the Top Ten SKUs to Excel and saved them, Excel had removed two leading zeros for each record. So when I'd gone back to SSMS to extract data for the Top Ten SKUs, using the list I had from Excel, the query didn't work because the SKUS didn't have the leading zeros. I didn't know that they'd impact the running of the code, but I've learnt something from the answers on this post (which I incorporated into my code), so thank you! – Mr 2017 Sep 18 '18 at 16:41
  • Oh.. @Mr2017 You must keep that it in mind and me to.. Which will save our future times.. – Pugal Sep 19 '18 at 07:47
  • Yeah, I definitely will! It's something that I didn't foresee, but your answers definitely helped, so thanks again for helping! – Mr 2017 Sep 20 '18 at 10:55