Solved

Error converting data type nvarchar to numeric.

Posted on 2011-02-21
4
742 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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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.
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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