Conversion failed when converting the VARCHAR value 'UNK' to data type int

Question

I am getting an error and would your help in getting it solved: Query:-

select      
    a11.ID_COMPANY ,
    a13.ID_CATEGORY  ,
    sum(a11.sales)  
from fact_table a11
join Dimension_Table a13 on (a11.ID_ITEM = a13.ID_ITEM )

Error:- Conversion failed when converting the varchar value 'G100015760' to data type int.

I have checked both fact table and the Dimension table for the ID_ITEM column have the data type of Varchar 20.

when I write cast it successfully runs the query .

select
    a11.ID_COMPANY ,
    a13.ID_CATEGORY  ,
    sum(a11.sales)  
from fact_table a11 
join Dimension_Table a13 on (a11.ID_ITEM = Cast(a13.ID_ITEM as varchar(20)))

So my doubt is when both columns are Varchar data type why I am getting an error and on explicitly adding a cast for the same datatype and length solves the issue.

I would urge you read your question and ask yourself if you think you would be able to answer based on only what you posted. Hint, it just a wall or partial details with nothing that is clear at all. Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
Sir, I am new to the forum , apology for creating confusion, I will try to explain my issue .I have posted 2 SQL queries , Query 1 where I am getting Conversion Error . Query 2 where I am using a Cast function in join condition which solves the issue . My doubt is why I have to write a Cast where the Datatype in both the Tables are same Varchar length 20 .Please let me know if my explanation does not make any sense — Amit
What is the datatype of a11.sales? I have a feeling that is the problem. — Sean Lange
Can you right click each table, select script table as and choose file or new query window to show the DDL for these tables? If you post the DDL it might help find your issue. — Joe C
Have you checked to see what table/column contains the problem value, 'G100015760'? — pmbAustin

Maurício Pontalti Neri Maurício Pontalti Neri · Accepted Answer · 2017-09-07T15:29:27

Your database is some problem with execution plans, check with your dba if the data maintenance plans are running. To solve it you can try to shrink the database.

Conversion failed when converting the VARCHAR value 'UNK' to data type int

1 Answers