how to solve a conversion failure error

Question

Post reply

how to solve a conversion failure error

Wren

SSC Enthusiast

Points: 178
More actions
October 18, 2024 at 5:10 pm

#4470061

Sample query reply appreciated
Getting the following error:
Conversion failed when converting the varchar value 'VZ34-031' to data type int.
This is my query that produced the error:
select count(t1.shipqty), t3.description from sales t1
left outer join return t2
on t1.company = t2.company
and t1.invoicenum = t2.invoicenum
left outer join reason t3
on t2.company = t3.company
and t2.returnreasoncode = t3.reasoncode
where t1.company = 'abc'
and t1.invoicedate between '2024-01-01' and '2024-06-01
and t1.product = 'one' or t1.product = 'two' or t1.product = 'three'
and t3.description = 'faulty device'
group by t1.shipqty, t3.description

November 3, 2024 at 9:06 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101123 More actions · Answer 1

No DDL provided, but my best guess is that one of these columns:

t1.invoicenum; t2.invoicenum

is integer and the other is not, and that one contains a value of 'VZ34-031'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76997 More actions · Answer 2

It looks like there may also be an error in your logic. ANDs are evaluated before ORs, and, based on your layout, that's not how you want the WHERE clause to be evaluated.

-- how this is interpreted.
where (t1.company = 'abc'
       and t1.invoicedate between '2024-01-01' and '2024-06-01'
       and t1.product = 'one')
 or t1.product = 'two'
 or (t1.product = 'three'
     and t3.description = 'faulty device')

-- how I suspect you want it based on your layout.
where t1.company = 'abc'
and t1.invoicedate between '2024-01-01' and '2024-06-01'
and (t1.product = 'one' or t1.product = 'two' or t1.product = 'three')  -- alternatively t1.product in ('one', 'two', 'three')
and t3.description = 'faulty device'

NOTE: I've used the {;} Code button to make this more presentable.

Drew

This reply was modified 2 months, 1 week ago by drew.allen.
This reply was modified 2 months, 1 week ago by drew.allen. Reason: Edited to put in missing end quotes

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

Adding to the above, this line in your WHERE clause is basically converting your LEFT JOINS to INNER JOINS

and t3.description = 'faulty device'

How to post data/code on a forum to get the best help.

P Jones SSChampion Points: 12361 More actions · Answer 4

You could use TRY_CONVERT(int, field) to ensure that you have an integer to work with in your query. If it fails to convert you'll get a null rather than an error.

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 5

P Jones wrote:

You could use TRY_CONVERT(int, field) to ensure that you have an integer to work with in your query. If it fails to convert you'll get a null rather than an error.

This is important, because the query optimizer will frequently ignore your where clauses and include the non-integer data. In other words, even if your where clause has where field not like '%-%', you will still get a conversion error.