CODEWITHSUNDEEP
sqlsql-server
Hossein Salmanian
Hossein Salmanian

Reputation: 763

'where' condition doesn't work in 'in' clause

I have three tables as follow

Orders [orderNo int]
OrderDetails [orderNo int , qty int]
StoreDocuments [docNo int, systemKey nvarChar(20)]

When I execute the following query to get orders that don't have any StoreDocuments, I get this error

Conversion failed when converting the nvarchar value '.' to data type int

Code:

Select 
    Sum(Dt.Qty) as SumQty
From
    Orders as [Or] 
inner join 
    OrderDetails as Dt on [Or].orderNo  = Dt.orderNo 
Where  
    [Or].orderNo NOT IN (Select SystemKey 
                         From StoreDocuments 
                         Where SystemKey <> '.')

The error is raised when there are no records in StoreDocuments that have a systemKey value of '.'.

My question is, why the where clause in inner query where systemkey <> '.' doesn't execute as it expected and it cause raising error?

Upvotes: 0

Views: 179

Answers (2)

Deep
Deep

Reputation: 3202

this should work for you :

Select Sum(Dt.Qty) as SumQty

From

Orders as [Or] inner join OrderDetails as Dt

on [Or].orderNo  = Dt.orderNo 

Where  cast([Or].orderNo as nvarchar(20))  Not IN

(

     Select SystemKey From StoreDocuments Where SystemKey <> '.'

)

sql fiddle

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

The reason for your exception is that SQL-Server tries to match your orderNo with any of the SystemKey entries. It does that by converting the SystemKey entries to an int prior to comparing. One of your entries is not convertible, i.e. it is not a number and so you get your exception.

What to do? Convert the orderNo to an NVarChar and then everything should be fine

Select Sum(Dt.Qty) as SumQty
  From Orders o 
       inner join OrderDetails as Dt
         on o.orderNo  = Dt.orderNo 
 Where cast(o.orderNo as NVarChar) Not IN
   (
     Select SystemKey From StoreDocuments Where SystemKey <> '.'
   )

Upvotes: 2

Related Questions