Data not filtering before a join.

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.

From stackoverflow

• Does:

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

  Fail by itself?

• 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.

  Stanislav Kniazev : Optimizer thing I think as well. where b.Employee_ID = CAST(empnum AS VARCHAR) should help

  Sam : I tried looking at the explain plan to see if I could spot it - but I'm not well versed in reading them. Your query worked well enough in the actual query. Thanks.

  tvanfosson : @Stanislav -- I wasn't sure what types each were so I thought converting both to varchar was the safest bet.

• 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

• 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.

  Sam : Really? It works fine on its own - the alias table b.

• 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.

  Sam : Unfortunatly the obvious answer is what we were doing first - and that surprisingly had the same behavior.