Data not filtering before a join.

Question

A puzzler from a coworker that I cannot figure out...

update  btd.dbo.tblpayroll
set     empname = ( select  b.Legal_Name
                    from    ( SELECT    Legal_Name,
                                        Employee_ID
                              FROM      Com.dbo.Workers
                              WHERE     isnumeric(Employee_ID) = 1
                            ) b
                    where   b.Employee_ID = empnum
                            and b.Legal_name is not NULL
                  )
where   empname is NULL

---

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'N0007 ' to data type int. The table alias b would actually be a view.

The value 'N0007 ' is in the Workers table. I don't see why it is not being filtered from the results that are being joined.

EDIT:

The alias does, in fact, return the correct rows - so isNumeric is doing the job.

Answer 1

Does:

SELECT    Legal_Name, Employee_ID
    FROM      Com.dbo.Workers
    WHERE     isnumeric(Employee_ID) = 1

Fail by itself?

Answer 2

I suspect that the optimizer is attempting to apply the where clause of the outer select before the inner select. Presumably it thinks it would be able to do an index lookup on Employee_ID resulting in a faster query in this case. Try:

update  btd.dbo.tblpayroll
set     empname = ( select Legal_Name
                    from Com.dbo.Workers
                    where  isnumeric(Employee_ID) = 1
                           and convert(varchar,Employee_ID)
                             = convert(varchar,empnum) 
                           and Legal_name is not NULL)
where   empname is NULL

Converting them all to varchar should take care of it. I don't think it's much less efficient than you wanted orginally since the isnumeric, if done first, would have forced a table scan anyway.

Answer 3

May be N is considered currency symbol ? You can try to replace IsNumeric with

LIKE REPLICATE('[0-9]',/*length of Employee_ID*/) or just LIKE '[0-9]%' if letter cannot be in the middle

Answer 4

ISNUMERIC() is famously unreliable for what you are trying to do. You'll need an alternative, which I've been asking for here like this one.

Answer 5

The obvious thing is to force the order of comparison, perhaps by getting then name from a view with only numeric Employee_IDs, rather than the full Workers table.