I have some data tables that where imported in from a flat file source so everything is
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_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
Other things I have tried that do not work:
TABLE_CODE = 'CLTDISC'in to the
onclause in the inner join.
OPTION (FORCE ORDER)query hint to the query.
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