Solved

Error converting data type nvarchar to numeric.

Posted on 2011-02-21
4
748 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 8

Author Comment

by:PeteEngineer
ID: 34941180

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
ID: 34941191
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
ID: 34941302
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
ID: 34941408
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

752 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