This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 76%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMA%3C/text%3E%3C/svg%3E)
My query is below, and I am not understanding the correlation of the error. FYI I have been running this query for several months on a daily basis and am only now getting this error.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'NONE' to data type int.
Begin
With Temp_CTE_278 as
(
Select aux.EpisodeID
from [Websphere].[dbo].[tblWebTXLog] as Wlog with (nolock)
Inner Join [Websphere].[dbo].[tblWebTXLogAux] as Aux with (nolock) on Wlog.LogId=Aux.LogID
INNER JOIN [CCNIDCTIER1SQL].[Pre-AuthThin].[dbo].[tblAuthorization] as Auth with (nolock) ON aux.EpisodeID = Auth.EpisodeID
Where Auth.AuthStatus='A' and Aux.TransactionSetCode =278 and Aux.Episodeid<>''
and Auth.InsCarrier like '1199%' and Aux.EpisodeDate>='2023-5-15'
)
Select Distinct Aux.EpisodeID,Aux.TransactionSetCode,Aux.EpisodeDate,Auth.AuthStatus
from [Websphere].[dbo].[tblWebTXLog] as Wlog with (nolock)
Inner Join [Websphere].[dbo].[tblWebTXLogAux] as Aux with (nolock) on Wlog.LogId=Aux.LogID
INNER JOIN [CCNIDCTIER1SQL].[Pre-AuthThin].[dbo].[tblAuthorization] as Auth with (nolock) ON aux.EpisodeID = Auth.EpisodeID
Where Auth.AuthStatus='A' and Aux.TransactionSetCode =270 and Aux.Episodeid<>''
and Auth.InsCarrier like '1199%' and Aux.EpisodeDate>='2023-5-15'
and Aux.EpisodeID Not In(select EpisodeID from Temp_CTE_278)
Order by Aux.EpisodeID,Aux.EpisodeDate Asc
End
Hi @Mastrorilli, Anastasia SC1
Do you have further question on this issue, could we offer more support?
If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
For example, maybe TransactionSetCode is a text column and you should write Aux.TransactionSetCode = '278' and Aux.TransactionSetCode = '270' instead of Aux.TransactionSetCode = 278 and Aux.TransactionSetCode = 270. Or maybe there are other columns that contain “NONE” instead of numbers.
OMG! that worked! so funny such a small thing, still don't know why it has been working all these months but not now, but appreciate the help!
Since Viorel's comment obviously answers the question, I took the liberty to convert it to an Answer, so that you can accept it.
Just to elaborate a little bit on this. In difference to most other typed languages, SQL has implicit conversion between strings and numbers. In my opinion, this is more a curse than a blessing, because it easily hides mistakes like this. Way back in SQL 6.5, you would have gotten a compile-time error for forgetting the quotes around 278.
A further aggravation is that when strings and numbers meet in an expression, the string is converted to number, due to the type-precedence list that SQL Server maintains.
Yet a complication is that things can seem to work for a long time. You may only have numeric data until one day you get some non-numeric data in that string column. There is also the joy of query optimization. Consider this:
SELECT convert(int, stringcol), ....
FROM tbl
WHERE <some condition that filters out non-numeric strings>
The optimizer may decide to apply the convert before it filters, which means that the query blows up, even if should not from a logical perspective. But it may also decide to apply the operations in the "correct" order, and the query runs fine. Until one day when the statistics has flipped, and the optimizer changes the order. And the query starts to fail.