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.

score 2 · Accepted Answer · answered Dec 03 '08 at 23:10

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.

answered Dec 03 '08 at 23:10

tvanfosson

524,688
99
697
795

Optimizer thing I think as well. where b.Employee_ID = CAST(empnum AS VARCHAR) should help – Stanislav Kniazev Dec 03 '08 at 23:42
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. – Sam Dec 03 '08 at 23:49
@Stanislav -- I wasn't sure what types each were so I thought converting both to varchar was the safest bet. – tvanfosson Dec 04 '08 at 00:30

score 0 · Answer 2 · edited Aug 31 '17 at 19:00

0

Maybe 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

edited Aug 31 '17 at 19:00

C B

1,677
6
18
20

answered Dec 03 '08 at 23:10

Dmitry Khalatov

4,313
3
33
39

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

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.

edited May 23 '17 at 12:01

Community

1
1

answered Dec 03 '08 at 23:43

Cade Roux

88,164
40
182
265

score 0 · Answer 4 · answered Dec 04 '08 at 21:46

0

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.

answered Dec 04 '08 at 21:46

SeaDrive

4,182
5
32
30

Unfortunatly the obvious answer is what we were doing first - and that surprisingly had the same behavior. – Sam Dec 05 '08 at 22:59

Data not filtering before a join

4 Answers4