Case Error Help

Question

select 
CASE WHEN  M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'ADJUSTABLE' THEN itm.new_product_number
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'REGULAR' THEN itm.new_product_number
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'LOW PRO' THEN itm.new_product_number
                                ELSE isnull(old_product_number,product_number) END  AS [Product Number]
                                from mfi_master_product m
                                
                                left outer  join 

			(select r.new_product_number,i.product_number, r.product_number old_product_number,
			--Product_Number, 
			Display_Product_Name, Search_Name, Product_Type, Product_Name
			, Item_Model_Group, Tracking_Dimension_Group, Storage_Dimension_Group
			, Product_Dimension_Group, Item_Group, Hierarchy, Category_Hierarchy
			, Color_Group, Size_group, Style_group, Segment, BRAND, CATEGORY, COMFORT
			, CONSTRUCTN, ACTIVE, PRICEPOINT, Vendor_Name
			from dbo.prod_missing_from_prod_dim_Rework i
			left join dbo.Replaced_Items r on i.product_number = r.new_product_number
			)  itm
			 on RIGHT(ITM_CD,5) = isnull(itm.old_product_number,itm.product_number)
			 

I am getting an error when i run the above code. Any ideas ?

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to float.

---

FM

Answer 1

I think you are comparing nvarchar datatype and float datatype some where in ur code.

please check the datatypes for the below columns

 r on i.product_number = r.new_product_number
 RIGHT(ITM_CD,5) = isnull(itm.old_product_number,itm.product_number)

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hello Farhan1,

It would help if you could post the create table statements (DDL) for the tables involved.

My best guess would be that old_product_number is a float and the issue is RIGHT(ITM_CD, 5) returns nvarchar.  If this is the case use:

convert(float, RIGHT(ITM_CD,5))

Cheers.

Answer 3

you are trying to match some varchar string with some float values. Please check the fields where you see this kind of comparison.

regards

joon