1

I have a very simple code that I am trying to run but for reasons that I do not understand, it is not working. Here's my code:

WITH CTE AS
(
    SELECT DISTINCT OrderNo
    FROM OrderDet
    WHERE PartNo LIKE '%.%'
      AND OrderNo NOT LIKE '%[a-z]%'
)
SELECT * 
FROM CTE
WHERE CAST(CTE.OrderNo AS INT) >= 21187

Running this code throws the following error:

Conversion failed when converting the varchar value '20361E' to data type int.

Now I am aware that there are OrderNo values that contain an 'E', however, as far as I'm concerned, I'm filtering them out in the CTE. If I run the statement in the CTE by itself, I get 580 records, none of which have any letters at all.

What's going on here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1452574
  • 485
  • 1
  • 6
  • 15
  • Welcome to the glorious world of SQL Optimization. There's no guarantee that the *actual* evaluation order of conditions matches what you perceive as a *logical* evaluation order. – Damien_The_Unbeliever Aug 30 '19 at 17:14
  • 1
    The better question is why you store an alphanumeric value as OrderNo and then try to treat it as a numeric value. – SMor Aug 30 '19 at 17:21
  • [This](https://stackoverflow.com/a/4596739/92546) answer may provide some clues. – HABO Aug 30 '19 at 17:21
  • What version of SQL Server are you on? If it's not out of support (or very nearly), it should have `TRY_CONVERT` that yields nulls rather than errors when it cannot convert. – Damien_The_Unbeliever Aug 30 '19 at 17:23
  • SQL 2012 and yes, I'm actually aware of TRY_CAST, which definitely works. However, I felt that using that is like a bandaid whereas my original query should 100% work and I'm just looking for an explanation as to why. It just seems so odd that it doesn't work as I intend – user1452574 Aug 30 '19 at 17:26
  • @user1452574 It's really handy that CTEs can (sometime) be treated as a single query. You can break up a huge query into reasonable steps and pull all of the pieces together at the end. During debugging you can `select` any of the intermediate results from the CTE. When you're done the query optimizer processes the whole smash as one query. (Okay, if you have things like recursion mixed in there it won't be optimized as a single query. YMMV.) – HABO Aug 30 '19 at 19:35

1 Answers1

8

As mentioned in the comments, you can't really control whether the CAST is attempted before the filter; it all depends on how SQL Server chooses to optimize the query.

Also, checking for the existence of a decimal and no a-z is not a very reliable way to filter out non-numerics.

Both of the following methods eliminate the need, I think, for the CTE, because you were only using it to try to force the filter to happen first. CTEs can be folded into the rest of the query; perhaps worth a read are some other tidbits about CTEs.

Try TRY_CONVERT() (which I wrote about here):

WHERE TRY_CONVERT(int, CTE.OrderNo) >= 21187;

If you are using an older version that doesn't support TRY_CONVERT, you can try CASE (perhaps another useful read):

WHERE CASE WHEN ISNUMERIC(CTE.OrderNo) = 1 THEN CTE.OrderNo END >= 21187;

But ISNUMERIC() is not all that reliable either.

Since now we know the version, your query can be simplified to:

SELECT DISTINCT OrderNo
  FROM dbo.OrderDet
  WHERE PartNo LIKE '%.%'
    AND TRY_CONVERT(int, OrderNo) >= 21187;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, I was not aware that you can't really control whether the CAST is attempted before the filter. I thought that 100% the CTE would execute first and the second query would execute EXCLUSIVELY on the CTE records. Kinda blows my mind that that's not the case – user1452574 Aug 30 '19 at 17:30