I have some data tables that where imported in from a flat file source so everything is varchar(max)
except FileRecordID
which is a int
that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)
The following query works perfectly
SELECT *
FROM [CLIENT]
left outer join
(
select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION
from [ENTRY]
inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
) as discounts on CLIENT_CLASS = ENTRY_CODE
where Client_recd = 'A'
--and client.[FileRecordID] = 10607
I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client
data table who has client.[FileRecordID] = 10607
it reports to me
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'BK/TN' to data type int.
The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE
in the first outer join. ENTRY
can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE
set to the line number from TABLE
where TABLE_CODE = 'CLTDISC'
will always be numbers only. I perform the cast as ENTRY_CODE
may or may not have leading 0's and spaces so I am trying to get them to be well formed.
What appears to me to be happening is adding client.[FileRecordID] = 10607
to the outer query causes the where
clause not to be evaluated before the cast to int in the inner query.
I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1
to both the inner and outer queries but it has no affect.
- How do I fix this?
- Can anyone explain what is happening so I can understand how to avoid this in the future?
Non filtered query planed execution
Filtered (non working) query planned execution
Notice the reversed order of the
Compute Scalar
and the Filter
Other things I have tried that do not work:
- Move
TABLE_CODE = 'CLTDISC'
in to theon
clause in the inner join. - Added
OPTION (FORCE ORDER)
query hint to the query.
Update:
I found a solution but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.
SELECT *
FROM [CLIENT]
left outer join
(
select ENTRY_CODE, ENTRY_DESCRIPTION
from [ENTRY]
inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')
where Client_recd = 'A'
and client.[FileRecordID] = 10607