Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error converting data type nvarchar to numeric.

Posted on 2011-02-21
4
Medium Priority
?
751 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 668 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 668 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 664 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

783 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