Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS Query Error

Answered SSRS Query Error

  • Sunday, March 03, 2013 7:21 PM
     
     
    Sign In to Vote
    0

    I am trying to sum the values of an order from order products (MS CRM) and the following throws an error #8169 Conversion failed when converting from a character string to a unique identifier.

    SELECT

    CRMAF_FilteredQuote.quoteid,
    SUM(CRMAF_FilteredQuoteDetail.new_extpriceatpurchase) AS NPITotal

    FROM
    FilteredQuote AS CRMAF_FilteredQuote
    INNER JOIN FilteredQuoteDetail AS CRMAF_FilteredQuoteDetail ON CRMAF_FilteredQuote.quoteid = CRMAF_FilteredQuoteDetail.quoteid

    WHERE
    CRMAF_FilteredQuoteDetail.new_sku_cat LIKE 'P-S-NPI-%' AND
    CRMAF_FilteredQuoteDetail.quoteid = @QuoteID

    GROUP BY CRMAF_FilteredQuote.quoteid

    Any ideas would be welcome!

    Ken Compter

     

All Replies

  • Sunday, March 03, 2013 7:48 PM
     
     
    Sign In to Vote
    0

    So it means that somewhere in your query you have an expression where one operator is a string and the other operator has the type uniqueidenfier, and the string is a not convertible to a GUID. Since it says "Character string", I place my bets on

      CRMAF_FilteredQuoteDetail.new_sku_cat LIKE 'P-S-NPI-%'

    That is, the column new_sku_cat is presumably of type uniqueidentifier. If that is not the case, you need to scrutinize the data type of the quoteid columns and the @QuoteID variable.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Sunday, March 03, 2013 8:04 PM
     
     
    Sign In to Vote
    0

    Thank you for your reply.  Here is what I tried next that works:

    SELECT

    SUM(CASE WHEN CRMAF_FilteredQuoteDetail.new_sku_cat LIKE 'P-S-NPI-%' THEN CRMAF_FilteredQuoteDetail.new_extpriceatpurchase ELSE 0 END)

    FROM FilteredQuote AS CRMAF_FilteredQuote
    INNER JOIN FilteredQuoteDetail AS CRMAF_FilteredQuoteDetail ON CRMAF_FilteredQuoteDetail.quoteid = CRMAF_FilteredQuote.quoteid

    WHERE
    CRMAF_FilteredQuoteDetail.new_sku_cat LIKE 'P-S-NPI-%'

    This gives me a sum of all products with "new_sku_cat LIKE 'P-S-NPI-%' ".  When I try to 'filter' by using a quoteid (a GUID), is when I get an error.  How do I filter based on the quoteid?

    Ken Compter

     
  • Sunday, March 03, 2013 8:16 PM
    Moderator
     
     
    Sign In to Vote
    0
    How is the @QuoteId defined and what is the value you're trying to pass for it?

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


    My blog

     
  • Sunday, March 03, 2013 8:20 PM
     
     
    Sign In to Vote
    0
    the QuoteID is the unique identifier for the quote ex. 7666237d-d1dd-de11-8156-00155d02202

    Ken Compter

     
  • Sunday, March 03, 2013 8:24 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    What is the exact value of the variable you're trying to use and how do you get it? For the value above I get the error

    select cast ('7666237d-d1dd-de11-8156-00155d02202' as uniqueidentifier)

    Conversion failed ...

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


    My blog

     
  • Monday, March 11, 2013 12:49 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Any progress?

    This is how you filter with uniqueidentifier:

    SELECT * FROM Production.Product
WHERE rowguid = '9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E';
GO
-- (1 row(s) affected)

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Tuesday, March 12, 2013 11:56 AM
     
     
    Sign In to Vote
    0
    Thank you all for replies.  Very helpful.

    Ken Compter