SQL Cast Mystery

Question

I have a real mystery with the T-SQL below. As it is, it works with either the DATAP.Private=1, or the cast as int on Right(CRS,1). That is, if I uncomment the DATAP.Private=1, I get the error Conversion failed when converting the varchar value 'M' to data type int, and if I then remove that cast, the query works again. With the cast in place, the query only works without the Private=1.

I cannot for the life of me see how the Private=1 can add anything to the result set that will cause the error, unless Private is ever 'M', but Private is a bit field!

SELECT 
   cast(Right(CRS,1) as int) AS Company
 , cast(PerNr as int) AS PN
 , Round(Sum(Cost),2) AS Total_Cost
FROM 
 DATAP 
 LEFT JOIN BU_Summary ON DATAP.BU=BU_Summary.BU
WHERE 
 DATAP.Extension Is Not Null 
 --And DATAP.Private=1
 And Left(CRS,2)='SB'
 And DATAP.PerNr Between '1' And '9A'
 and Right(CRS,1) <> 'm'
GROUP BY 
   cast(Right(CRS,1) as int)
 , cast(PerNr as int)
ORDER BY 
 cast(PerNr as int)

Answer 1

I've seen something like this in the past. It's possible the DATAP.Private = 1 clause is generating a query plan that is performing the CRS cast before the Right(CRS,1) <> 'm' filter is applied.

It sure shouldn't do that, but I've had similar problems in T-SQL I've written, particularly when views are involved.

You might be able to reorder the elements to get the query to work, or select uncast data values into a temporary table or table variable and then select and cast from there in a separate statement as a stopgap.

If you check the execution plan it might shed some light about what is being calculated where. This might give you more ideas as to what you might change.

Answer 2

Just a guess, but it may be that when Private = 1, PerNr cannot be anything but a castable number in your data (as it is in the PerNr can equal '9A [or whatever else]', breaking the cast in the group by and order by clauses).

Answer 3

CAST('9A' AS int) fails when I tested it. It looks like you're doing unnecessary CASTS for grouping and sorting. Particularly in the GROUP BY, that will at least kill any chance for optimizing.