• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Error converting data type nvarchar to numeric.

Msg 8114, Level 16, State 5, Line 7
Error converting data type nvarchar to numeric.

Why i am getting this above error at line :

MAX(CASE rtt.usage_code WHEN N''r'' THEN rtt.name END) AS input_tax_name  
0
PeteEngineer
Asked:
PeteEngineer
3 Solutions
 
PeteEngineerAuthor Commented:

SELECT top 5 i.name AS item_name,
       ih.name  AS category_name,
       sib.barcode_number  As barcode_number,
       MAX(CASE rtt.usage_code WHEN N''r'' THEN rtt.name END) AS input_tax_name,
       MAX(CASE rtt.usage_code WHEN N''r'' THEN ISNULL(sisttv.tax_percentage,0) END) AS input_tax_rate,
       MAX(CASE rtt.usage_code WHEN N''s'' THEN rtt.name END) AS output_tax_name,
       MAX(CASE rtt.usage_code WHEN N''s'' THEN ISNULL(sisttv.tax_percentage,0) END) AS output_tax_rate
 FROM (SELECT * FROM  r_tax_ty AS rtt WHERE  client_id in (@cdm_client_id, 0)) AS rtt
 JOIN (SELECT * FROM  S_view AS sisttv WHERE  client_id in (@cdm_client_id, 0)) AS sisttv
   ON rtt.r_tax_ty_id = sisttv.r_tax_ty_id
  AND sisttv.retail_tax_hierarchy_id = rtt.retail_tax_hierarchy_id
 JOIN (SELECT * FROM  s_it_bar sib WHERE  client_id in (@cdm_client_id, 0)) sib
   ON sib.supplier_item_id = sisttv.supplier_item_id 
 JOIN (SELECT * FROM  item AS i WHERE  client_id in (@cdm_client_id, 0)) AS i
   ON sisttv.item_id = i.item_id
LEFT OUTER JOIN (SELECT * FROM  i_hie ih WHERE  client_id in (@cdm_client_id, 0)) ih
   ON ih.i_hie_id = i.i_hie_id
 WHERE rtt.retail_tax_hierarchy_id = sisttv.retail_tax_hierarchy_id
  AND i.name like COALESCE(@name_filter, i.name)
  AND sisttv.business_unit_id = @business_unit_id
  AND ( i.item_hierarchy_id in (
      ((SELECT item_hierarchy_id
          FROM item_hierarchy_list 
         WHERE parent_item_hierarchy_id = COALESCE( @item_hierarchy_id, i.item_hierarchy_id )
         UNION 
        SELECT COALESCE( @item_hierarchy_id, i.item_hierarchy_id ) ))  ))
AND i.item_type_code NOT IN ( 'e', 's', 'r', 'j', 'k' )
GROUP BY i.name,ih.name,sib.barcode_number,sisttv.tax_percentage

Open in new window

0
 
xiong8086Commented:
MAX() is a group function, you need to have Group By clause,

and Max() works on numeric, but you are trying to have MAX(rtt.name), which doesn't work properly.
0
 
pivarCommented:
Hi,

The error (line 7) isn't at

MAX(CASE rtt.usage_code WHEN N''r'' THEN rtt.name END) AS input_tax_name  

instead check the clause:

WHERE rtt.retail_tax_hierarchy_id = sisttv.retail_tax_hierarchy_id
  AND i.name like COALESCE(@name_filter, i.name)
  AND sisttv.business_unit_id = @business_unit_id
  AND ( i.item_hierarchy_id in (
      ((SELECT item_hierarchy_id
          FROM item_hierarchy_list
         WHERE parent_item_hierarchy_id = COALESCE( @item_hierarchy_id, i.item_hierarchy_id )
         UNION
        SELECT COALESCE( @item_hierarchy_id, i.item_hierarchy_id ) ))  ))
AND i.item_type_code NOT IN ( 'e', 's', 'r', 'j', 'k' )

/peter
0
 
ThomasianCommented:
You need to repeat the single quote character inside a string
       MAX(CASE rtt.usage_code WHEN N'''r''' THEN rtt.name END) AS input_tax_name,
       MAX(CASE rtt.usage_code WHEN N'''r''' THEN ISNULL(sisttv.tax_percentage,0) END) AS input_tax_rate,
       MAX(CASE rtt.usage_code WHEN N'''s''' THEN rtt.name END) AS output_tax_name,
       MAX(CASE rtt.usage_code WHEN N'''s''' THEN ISNULL(sisttv.tax_percentage,0) END) AS output_tax_rate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now