Possible invalid data?

Question

Possible invalid data?

ben.rosato

SSCommitted

Points: 1929
More actions
April 9, 2009 at 7:45 am

#155541

Not sure what to make of this. I've never had it happen before.
Here's a query that fails with the following error.
Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '4.5' to a column of data type int.
SELECT TOP 100
c.[iIncidentId],
A.[iQuoteId],
B.[iLineNumberId],
d.[vchEmailAddress],
d.[vchAddress1],
d.[vchAddress2],
d.[vchCity],
d.[chRegionCode],
d.[vchPostCode],
d.[chCountryCode],
d.[vchPhoneNumber],
b.[chProductNumber],
b.[flQuantity],
b.[flUnitPrice],
a.[chCurrencyCode],
a.[flUserFlt1] AS Shipping,
e.[TaxAmount]
FROM
[QuoteHeader] A INNER JOIN [QuoteDetail] B
ON A.iSiteId = B.iSiteID AND A.iQuoteID = B.IQuoteID
INNER JOIN [Incident] C
ON A.[iSystemId] = C.[iIncidentId]
INNER JOIN [Individual] D
ON c.[iContactId] = d.[iIndividualId]
INNER JOIN storedev.dbo.orders E
ON A.[iSystemId] = E.[IncidentID]
WHERE
A.[iSiteId] = 1 AND
A.[iStatusId] = 102035 AND --won
A.[vchUser3] in (103235, 103236) AND --stdordw, trnordw
E.IncidentID <> 0
ORDER BY
A.[iQuoteId] desc
If you change the last join to the following it works:
Inner JOIN store.dbo.orders E
ON CAST(A.[iSystemId] AS NVARCHAR(25)) = CAST(E.[IncidentID] AS NVARCHAR(25))
Both of those colums are indeed int

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ben.rosato (4/9/2009)
If you change the last join to the following it works:
Inner JOIN store.dbo.orders E
ON CAST(A.[iSystemId] AS NVARCHAR(25)) = CAST(E.[IncidentID] AS NVARCHAR(25))
Both of those colums are indeed int

Try this: Inner JOIN store.dbo.orders E

ON A.[iSystemId] = CAST(E.[IncidentID] AS NVARCHAR(25))

And this:

Inner JOIN store.dbo.orders E

ON CAST(A.[iSystemId] AS NVARCHAR(25)) = E.[IncidentID]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ben.rosato SSCommitted Points: 1929 More actions · Answer 2

Chris Morris (4/9/2009)
ben.rosato (4/9/2009)
If you change the last join to the following it works:
Inner JOIN store.dbo.orders E
ON CAST(A.[iSystemId] AS NVARCHAR(25)) = CAST(E.[IncidentID] AS NVARCHAR(25))
Both of those colums are indeed int
Try this: Inner JOIN store.dbo.orders E
ON A.[iSystemId] = CAST(E.[IncidentID] AS NVARCHAR(25))
And this:
Inner JOIN store.dbo.orders E
ON CAST(A.[iSystemId] AS NVARCHAR(25)) = E.[IncidentID]

Same error in both cases.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Then a brief process of elimination:

SELECT E.[IncidentID], CAST(E.[IncidentID] AS NVARCHAR(25))

FROM store.dbo.orders E

WHERE CAST(E.[IncidentID] AS NVARCHAR(25)) LIKE '%.%'

Edit to add comment: This is the only column which doesn't have an "i" prefix;-)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ben.rosato SSCommitted Points: 1929 More actions · Answer 4

Chris Morris (4/9/2009)
Then a brief process of elimination:
SELECT E.[IncidentID], CAST(E.[IncidentID] AS NVARCHAR(25))
FROM store.dbo.orders E
WHERE CAST(E.[IncidentID] AS NVARCHAR(25)) LIKE '%.%'
Edit to add comment: This is the only column which doesn't have an "i" prefix;-)

I tried that already. No records returned with either column.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

toA.[vchUser3] in ('103235', '103236')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 6

I would check the schemas for each table involved in the query to see which column in which table could be the problem.

ben.rosato SSCommitted Points: 1929 More actions · Answer 7

Chris Morris (4/9/2009)
Next:
A.[vchUser3] in (103235, 103236)
toA.[vchUser3] in ('103235', '103236')

Well, that'll learn me. This was sent to me by someone else and I didn't really verify if the 2 CASTS actually fixed it even though he said it did. It didn't but that's where I was focused and I missed the obvious. vchUser3 is nvarchar!

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

ben.rosato (4/9/2009)
Well, that'll learn me. This was sent to me by someone else and I didn't really verify if the 2 CASTS actually fixed it even though he said it did. It didn't but that's where I was focused and I missed the obvious. vchUser3 is nvarchar!
Thanks

heh I bet the two casts "fixed it" by eliminating a row with a vchUser3 value containing an embedded "."!

Top work Ben 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden