SQL Inner Join Help Needed

Question

I have the following query below:

SELECT 
[dbo].[history].histtable,
[dbo].[history].code,
[dbo].[history].Date,
dbo.Activities.Number,
dbo.table1.Itemnumber

FROM [dbo].[History]
INNER JOIN 
[dbo].[Activities]
ON
[dbo].[History].Flags=[dbo].[Activities].Objects

INNER JOIN dbo.table1 
On
CONVERT (Int, dbo.table1.Itemnumber) = CONVERT (INT, dbo.history.Date)
OR CONVERT (Int, dbo.tablenumber.Itemnumber) = dbo.Activities.Alltransactions

ORDER BY 3 DESC

and I keep getting the following error:

Conversion failed when converting the varchar value Itemnumber to data type int.

What can I do to fix this?

Answer 1

you can do a separate select for each table that you are joining and use ISNUMERIC to pull out only the rows that are not numeric so you can better identify your problem and see what values are not numeric

something like

select Itemnumber
from dbo.table1 
where IsNumeric(Itemnumber) = 0

or in you join, something like

INNER JOIN dbo.table1 
On IsNumeric(dbo.table1.Itemnumber) = 1 
and 
(
    CONVERT (Int, dbo.table1.Itemnumber) = CONVERT (INT, dbo.history.Date)
OR
    CONVERT (Int, dbo.tablenumber.Itemnumber) = dbo.Activities.Alltransactions
)