Why this error message?

Question

SQL server 2008 R2 and SQL reporting services 2008

When I run my query in Management studio, I do not get any error messages, but when I try to run it in Query Designer in Reporting Services I get an error message saying "Error Message: Operand data type varchar is invalid for subtract operator."

Can someone tell me what is wrong?

with cte as (Select ie.Market,
  ie.[Sales Area_Buyer],
  ie.[Statisticscustomer_Supplierno_],
  ie.[Material No_],
  ma.[Description],
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 1 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY1',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Amount] ELSE 0 END)*-1 AS 'InvCY',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Src_ Curr_ Amount] ELSE 0 END)*-1 AS 'InvCYLC',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Adjusted Cost (Invoiced Qty_)] ELSE 0 END)*-1 AS 'COGS'
from [STIHL SE$Invoice Entries] as ie
join Material as ma
on ma.No_=ie.[Material No_]
where (ie.[Entry Type]=1)
and (MONTH(ie.[Posting Date]) between @StartManad and @StoppManad)
and (ie.[Market] in (@Market))
and (ie.[Sales Area_Buyer] in (@SalesArea))
and (YEAR([Posting Date])>YEAR(Getdate())-3)
Group by ie.Market,ie.[Sales Area_Buyer],ie.[Statisticscustomer_Supplierno_], ie.[Material No_], ma.[Description])

Select Market,
  [Sales Area_Buyer],
  [Statisticscustomer_Supplierno_],
  [Material No_],
  [Description],
  DelCY2,
  DelCY1,
  DelCY,
  InvCY,
  InvCYLC,
  COGS,
  (COGS-InvCY)*-1 as TB,
  (((COGS-InvCY)*-1/NULLIF(InvCY,0))*100) as TG
FROM cte
Order by Market, [Sales Area_Buyer], [Statisticscustomer_Supplierno_], [Material No_]

Answer 1

I have no idea about this specific error, but it does sound like the issue with with the Query Designer.

Could you add "set nocount on;" to the top of your statement and see if this helps in any way?

Alternatively, you might be able to get the statement to run by creating a proc with the CTE and calling the proc from SSRS.

mark

Answer 2

Hello Mark and thank you for your answer.

The SET statement is unfortunately not supported in Query Designer.

The problem is that if the code is not ok in Query Designer, I will not get any fields when I add the data set.

I am not a programmer, so I do not know how to create a procedure, but I have been thinking of calling a view....

Answer 3

you need to put semicolon in front of the with. start your stmt with ;with cte as ( ....

good luck

---

Think out of the box

Answer 4

hello i_h,

what do you mean? What does the semicolon do?

 When I try to do that I just get another error message.

Answer 5

I don't think that the semi-colon will do anything.  In fact, I don't think that you really need "set nocount on;" either.  Just a shot in the dark.  For the error message that you received regarding "set", you can hit ignore and click on the edit as text button.  This would allow you to use the set command in the query.

On another note, the way that you are using the CTE looks like you may have adopted this from another query.  You don't really need it at all and it might help to take it out to keep things simple.  CTE's can be used to get year over year comparisons, but you would sum the year and then join the cte to itself. 

To simplify your query, try nesting it:

 Select Market,
   [Sales Area_Buyer],
   [Statisticscustomer_Supplierno_],
  [Material No_],
  [Description],
   DelCY2,
   DelCY1,
   DelCY,
   InvCY,
   InvCYLC,
   COGS,
   (COGS-InvCY)*-1 as TB,
   (((COGS-InvCY)*-1/NULLIF(InvCY,0))*100) as TG
 FROM (
 Select ie.Market,
   ie.[Sales Area_Buyer],
   ie.[Statisticscustomer_Supplierno_],
  ie.[Material No_],
  ma.[Description],
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2',
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 1 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY1',
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY',
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Amount] ELSE 0 END)*-1 AS 'InvCY',
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Src_ Curr_ Amount] ELSE 0 END)*-1 AS 'InvCYLC',
   SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Adjusted Cost (Invoiced Qty_)] ELSE 0 END)*-1 AS 'COGS'
 from [STIHL SE$Invoice Entries] as ie
 join Material as ma
 on ma.No_=ie.[Material No_]
 where (ie.[Entry Type]=1)
 and (MONTH(ie.[Posting Date]) between @StartManad and @StoppManad)
 and (ie.[Market] in (@Market))
 and (ie.[Sales Area_Buyer] in (@SalesArea))
 and (YEAR([Posting Date])>YEAR(Getdate())-3)
 Group by ie.Market,ie.[Sales Area_Buyer],ie.[Statisticscustomer_Supplierno_], ie.[Material No_], ma.[Description])tb

The order by is not necessary because SSRS will resort the data any way. 

For further troubleshooting of your original error, take out columns until you find the one that's giving you grief.  From the syntax, I can't see anything wrong, but the error could be elsewhere.  If you strip this thing down to the simplest query, get rid of the parameters and everything and get it to work, you will then be able to start adding columns back in row by row until you reproduce the problem, then you will at least know the exact source of the error.

Mark

Answer 6

Hi,

Try to use temp table instead of CTE and see if it works or not, something like below :

IF OBJECT_ID('Tempdb..#T') IS NOT NULL DROP TABLE #T
Select ie.Market,
  ie.[Sales Area_Buyer],
  ie.[Statisticscustomer_Supplierno_],
  ie.[Material No_],
  ma.[Description],
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 1 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY1',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Amount] ELSE 0 END)*-1 AS 'InvCY',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Src_ Curr_ Amount] ELSE 0 END)*-1 AS 'InvCYLC',
  SUM(CASE year([Posting Date]) WHEN year(Getdate()) THEN ie.[Adjusted Cost (Invoiced Qty_)] ELSE 0 END)*-1 AS 'COGS'
Into #T 
from [STIHL SE$Invoice Entries] as ie
join Material as ma
on ma.No_=ie.[Material No_]
where (ie.[Entry Type]=1)
and (MONTH(ie.[Posting Date]) between @StartManad and @StoppManad)
and (ie.[Market] in (@Market))
and (ie.[Sales Area_Buyer] in (@SalesArea))
and (YEAR([Posting Date])>YEAR(Getdate())-3)
Group by ie.Market,ie.[Sales Area_Buyer],ie.[Statisticscustomer_Supplierno_], ie.[Material No_], ma.[Description])

Select Market,
  [Sales Area_Buyer],
  [Statisticscustomer_Supplierno_],
  [Material No_],
  [Description],
  DelCY2,
  DelCY1,
  DelCY,
  InvCY,
  InvCYLC,
  COGS,
  (COGS-InvCY)*-1 as TB,
  (((COGS-InvCY)*-1/NULLIF(InvCY,0))*100) as TG
FROM #T
Order by Market, [Sales Area_Buyer], [Statisticscustomer_Supplierno_], [Material No_]

---

Thanks, Sumit