Solved

Error converting data type nvarchar to numeric.

Posted on 2011-02-21
4
738 Views
Last Modified: 2012-05-11
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
Comment
Question by:PeteEngineer
4 Comments
 
LVL 8

Author Comment

by:PeteEngineer
Comment Utility

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
 
LVL 3

Accepted Solution

by:
xiong8086 earned 167 total points
Comment Utility
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
 
LVL 22

Assisted Solution

by:pivar
pivar earned 167 total points
Comment Utility
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 166 total points
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now