October 18, 2024 at 5:10 pm
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
October 18, 2024 at 6:19 pm
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".
October 18, 2024 at 11:02 pm
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply