none
Operand data type varchar is invalid for subtract operator

    Question

  • SQL server 2008 R2

    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, 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_]

    Friday, April 13, 2012 11:52 AM

Answers

  • Chances are the error message is pointing to the "WITH" line, I suspect?  (Don't you hate that?)  Anyway, try running the query itself (just for proof of concept), not the CTE:  (as in, the part highlighted below):  It should give you a specific line number where the error occurs, which may expose something else).  Are you running the query against the same database in both scenarios?

    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_]

    Also, perhaps try the longer-winded version of the CASE statement (for both of your Case statements that do the subtracting):

    SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2'  -- Current way, uses shorter syntax.
    
    SUM(CASE WHEN year([Posting Date]) =  year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2' -- Try the longer version of the CASE statement

    Friday, April 13, 2012 6:23 PM

All replies

  • It could be a bug in the Query Designer, so I would not give it to much thought.


    AMB

    Some guidelines for posting questions...

    Friday, April 13, 2012 12:28 PM
  • Try:

    with cte as (Select ie.Market,
      ie.[Sales Area_Buyer],
      ie.[Statisticscustomer_Supplierno_], 
      ie.[Material No_], 
      ma.[Description],
      -1*SUM(CASE WHEN year([Posting Date]) = year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END) AS 'DelCY2',
      -1*SUM(CASE WHEN year([Posting Date]) =year(Getdate()) - 1 THEN ie.[Invoiced Quantity] ELSE 0 END) AS 'DelCY1',
      -1*SUM(CASE WHEN year([Posting Date]) =year(Getdate()) THEN ie.[Invoiced Quantity] ELSE 0 END) AS 'DelCY',
      -1*SUM(CASE WHEN year([Posting Date]) =year(Getdate()) THEN ie.[Amount] ELSE 0 END) AS 'InvCY',
      -1*SUM(CASE WHEN year([Posting Date]) =year(Getdate()) THEN ie.[Src_ Curr_ Amount] ELSE 0 END) AS 'InvCYLC',
      -1*SUM(CASE WHEN year([Posting Date]) = year(Getdate()) THEN ie.[Adjusted Cost (Invoiced Qty_)] ELSE 0 END) 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 [Posting Date]>dateadd(year,-3,Getdate())
    and MONTH(ie.[Posting Date]) between @StartManad and @StoppManad
    and ie.[Market] in (@Market)
    and ie.[Sales Area_Buyer] in (@SalesArea)
    
    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,
      InvCY -COGS as TB,
      ((InvCY-COGS)/NULLIF(InvCY,0))*100.0 as TG
    FROM cte
    Order by Market, [Sales Area_Buyer], [Statisticscustomer_Supplierno_], [Material No_]
    
    You can simplify the above a little. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, April 13, 2012 12:37 PM
  • Thank you Naomi for your answer, but unfortunately it did not help. I'm thinking more like Hunchback, that it is a bug in Query Designer. I tried to create a view in Query Designer and got the same error message.

    Friday, April 13, 2012 12:46 PM
  • It can be a bug in the Query Designer. I suggest to turn the above select statement into a stored procedure. Also, I suggest to try to optimize it - I made a quick attempt of optimizing the simplest stuff, but I think if you will use ranges for Posting Date instead of not sargable MONTH([Posting Date]) expression you will get better performance. 

    Also, just to test - if you remove -1* from the query, does it work?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, April 13, 2012 12:50 PM
  • Andreas,

    My advice is that if your intention is to learn T-SQL, then forget about the Query Designer.


    AMB

    Some guidelines for posting questions...

    Friday, April 13, 2012 12:52 PM
  • Actually I am trying to create a report in SQL Reporting Services. I need the T-SQL code to be accepted by Query Designer, otherwise it will just create an empty dataset with no fields.
    Friday, April 13, 2012 1:05 PM
  • You can use stored procedure for your report instead of direct query. It may be a better idea especially if the query is relatively complex (as in your case).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, April 13, 2012 1:06 PM
  • Have you tried putting a semi colon in front of your

    ;with cte

    The ssrs query designer has always been a little goofy with complex queries - generally a better idea to just use a stored procedure


    Chuck

    Friday, April 13, 2012 1:07 PM
  • One more thought - do you see this same issue when you just paste the code into the query textbox in the ssrs dataset and press refresh fields?

    Chuck


    Friday, April 13, 2012 1:27 PM
  • The semicolon just just gives me "Unable to parse the query text"
    Friday, April 13, 2012 1:32 PM
  • Yes. Same result. :(

    Friday, April 13, 2012 1:34 PM
  • From  my perspective your options are:

    1) Use a stored procedure

    2) Open a support ticket with MS and wait


    Chuck

    Friday, April 13, 2012 1:39 PM
  • I think I'll go for a stored procedure. Thanks.
    Friday, April 13, 2012 1:40 PM
  • Chances are the error message is pointing to the "WITH" line, I suspect?  (Don't you hate that?)  Anyway, try running the query itself (just for proof of concept), not the CTE:  (as in, the part highlighted below):  It should give you a specific line number where the error occurs, which may expose something else).  Are you running the query against the same database in both scenarios?

    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_]

    Also, perhaps try the longer-winded version of the CASE statement (for both of your Case statements that do the subtracting):

    SUM(CASE year([Posting Date]) WHEN year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2'  -- Current way, uses shorter syntax.
    
    SUM(CASE WHEN year([Posting Date]) =  year(Getdate()) - 2 THEN ie.[Invoiced Quantity] ELSE 0 END)*-1 AS 'DelCY2' -- Try the longer version of the CASE statement

    Friday, April 13, 2012 6:23 PM
  • Hello John,

    thanks for your interest in this.

    I did as you suggested and the query run by itself works just fine. I then took away the cte part and made two queries of it where I saved the first query in a temp table. Don't know if this is the right way to do this, but it worked for me.

    Monday, April 16, 2012 12:40 PM