Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Confusing data type conversion error

    I am using SQL Server 2008

    The below SQL statement works.

    Code:
    SELECT TOP (100) PERCENT Name, CASE WHEN COALESCE (NULL, [Form], - 1, 0) = - 1 THEN 'Menu' ELSE 'Form' END AS Form_or_Menu, Parent, Form
    FROM  dbo.Menu
    WHERE (Parent = 'Reports')
    ORDER BY [Order]
    The below SQL statement does not work.

    Code:
    SELECT TOP (100) PERCENT Name, CASE WHEN COALESCE (NULL, [Form], - 1, 0) = - 1 THEN 'Menu' ELSE 'Form' END AS Form_or_Menu, Parent, Form
    FROM  dbo.Menu
    WHERE (Parent = 'Sample Reports')
    ORDER BY [Order]
    Not that the only difference is...

    Code:
    WHERE (Parent = 'Reports')
    Code:
    WHERE (Parent = 'Sample Reports')
    The Parent field is a nvarchar(255) datatype and I don't know if that has anything to do with this. It doesn't make sense to me because the field has records with spaces.

    [Edit]
    Sorry I forgot to include the error itself.

    When replicating the error, I have found that it only occurs with a specific string. For example if i have...

    Code:
    WHERE (Parent = 'Rep orts')
    It works.

    This however...

    Code:
    WHERE (Parent = 'Sample Reports')
    Does not work.

    The error is...

    SQL Execution Error.
    Executed SQL statement: SELECT TOP (100) PERCENT Name, CASE WHEN COALESCE (NULL, [Form], - 1, 0) = - 1 THEN 'Menu' ELSE 'Form' END AS Form_or_Menu, Parent, Form FROM dbo.Menu WHERE (Parent = 'Sample Reports') ORDER BY [Order]
    Error Source: .Net SqlClient Data Provider
    Error Message: Conversion failed when converting the nvarcharvalue '(Data summary reports) Paired Comparison Generation' to data type int.
    Last edited by Access Junkie; 05-07-09 at 06:08.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    There is a non numeric value in the [Form] column for Parent = 'Sample Reports'

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you explain what you want to achieve with this code ?
    Code:
    COALESCE (NULL, [Form], - 1, 0) = - 1
    COALESCE will return the first non-NULL value in the sequence of parameters. So with "NULL, [Form], - 1, 0", the NULL will always be skipped to [Form], if [Form] is NULL, COALESCE will return -1. The tailing 0 will never be returned.

    So you will get the same result by writing
    Code:
    COALESCE ([Form], -1) = -1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...and better performance from:
    Code:
    ...WHEN [Form] = -1 OR [Form] IS NULL THEN....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2006
    Posts
    72
    Thank you for the advice. I had the wrong idea about how COALESCE is supposed to work. What I actually wanted and actually works is below.

    Code:
    SELECT TOP (100) PERCENT Name, CASE WHEN [Form] IS NULL THEN 'Menu' ELSE 'Form' END AS Form_or_Menu, Parent, Form 
    FROM Menu 
    WHERE Parent='Reports' 
    ORDER BY [Order]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •