none
Conversion Failed when Converting the nvarchar value 'N' to data type int.

    Question

  • Hi..

    I am facing a strange situation. when i executing the following query it is returning the error

    SELECT  s.row_id as shipment, 
      SUM(CAST(la5.attr_value AS INT)) AS Cases    
    FROM 
      shipment s WITH(NOLOCK) 
      INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id 
      INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id 
      INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND 
            s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local  
      INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3) 
    WHERE s.spare2 = 'RELEASED'    
      AND shift.shift_desc = 'c'
      AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000') 
      AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000') 
      AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000')
    GROUP BY s.row_id 

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the nvarchar value 'N' to data type int.

    The column  type of attr_value of lot_attr table is a USER DEFINED DATATYPE (Based on nvarchar datatype). containing both numeric type and non numeric type of data.

    But when i executing the following query

    SELECT  s.row_id as shipment, 
      --SUM(CAST(la5.attr_value AS INT)) AS Cases    
    la5.attr_value  AS Cases
    FROM 
      shipment s WITH(NOLOCK) 
      INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id 
      INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id 
      INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND 
            s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local  
      INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3) 
    WHERE s.spare2 = 'RELEASED'    
      AND shift.shift_desc = 'c'
      AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000') 
      AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000') 
      AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000')
    --and isnumeric(la5.attr_value)=1
    --GROUP BY s.row_id 

    It is returning data like ..

    Shipment    Cases

    12436          40
    12436          40
    12436          27
    12437          27
    12437          24
    12437          24
    12437          40

    We can see there is no non numeric type data fetched..

    if we executing the following query we are geting result what is expected .. just I  have added  isnumeric(la5.attr_value)=1  in the WHERE condition

    SELECT  s.row_id as shipment, 
      SUM(CAST(la5.attr_value AS INT)) AS Cases    
    FROM 
      shipment s WITH(NOLOCK) 
      INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id 
      INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id 
      INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND 
            s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local  
      INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3) 
    WHERE s.spare2 = 'RELEASED'    
      AND shift.shift_desc = 'c'
      AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000') 
      AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000') 
      AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000')
    and isnumeric(la5.attr_value)=1
    GROUP BY s.row_id

    Shipment    Cases

    12436             107

    12437             115

    My question is if there is no Non Numeric data selecting in that particular query the why I am geting error in the first query. In the Second query you can see.. there i no non numeric data found.. and in the Thrid query you can see if we add ISNUMERIC() FUNCTION then it is executing without returning any error..

     


    Conversion failed when converting the nvarchar value 'N' to data type int.


    SD
    Tuesday, August 17, 2010 5:06 PM

All replies

  • Hello,

    Please, could you have a look on this link ?

    http://msdn.microsoft.com/en-us/library/ms187928(v=SQL.100).aspx

    You will find just before the part Examples

    SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal

    When you add the ISNUMERIC, you skip all data which cannot be converted in numbers ==> no error possible

    Don't hesitate to post again for more help or explanations

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, August 17, 2010 8:22 PM
  • Hi Thanks for your reply.. but see my query..  When I am doing SUM without applying ISNUMERIC() function it is returning Error..

    Then I just wanted to see what is the resultset for which we are doing sum..

    SELECT  s.row_id as shipment, 
      --SUM(CAST(la5.attr_value AS INT)) AS Cases    
    la5.attr_value  AS Cases
    FROM 
      shipment s WITH(NOLOCK) 
      INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id 
      INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id 
      INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND 
            s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local  
      INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3) 
    WHERE s.spare2 = 'RELEASED'    
      AND shift.shift_desc = 'c'
      AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000') 
      AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000') 
      AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000')
    --and isnumeric(la5.attr_value)=1
    --GROUP BY s.row_id 

    It is returning data like ..

    Shipment    Cases

    12436          40
    12436          40
    12436          27
    12437          27
    12437          24
    12437          24
    12437          40

    This is the total records fetched.. There is no non numeric data shown apparently..

    Then again I am executing the SUM query with ISNUMERIC() function that query returning the result what is expected i.e the sum of

    Shipment    Cases

    12436          40
    12436          40
    12436          27
    12437          27
    12437          24
    12437          24
    12437          40

    Shipment    Cases

    12436             107

    12437             115

    so if there is any non numeric data in that query in that case the Sum data should be less ..

     


    SD
    Wednesday, August 18, 2010 3:32 AM
  • May be there is field having value 'N' which is not coming in the current result set.
    Wednesday, August 18, 2010 7:43 AM
  • Abbas .. if you see the row fetched.. when we are not doing any sum and not applying ISNUMERIC() function..

    Shipment    Cases

    12436          40
    12436          40
    12436          27
    12437          27
    12437          24
    12437          24
    12437          40

    You can see there is no data which contained any non numeric ..

     


    SD
    Wednesday, August 18, 2010 11:19 AM
  • Abbas.. i have checked the data .. i mean whatever data is coming useing the same where condition.. all are numeric type..
    SD
    Monday, August 23, 2010 10:22 AM