CODEWITHSUNDEEP
sql-servert-sql
user1452574
user1452574

Reputation: 485

I thought the order of items of the WHERE clause didn't matter, why does it matter in my code?

This doesn't work:

SELECT
    CAST(o.OrderNo AS INT) AS [OrderNo], 
    DATEADD(d, 7, CAST(SUBSTRING(o.User_Memo1, 
    CHARINDEX('Pack',o.User_Memo1)+5, 8) AS DATE)) AS [1 Week After Prod Due],
    o.User_Date1 AS [3 Week Ack Date]  
FROM Orders o 
    JOIN OrderDet od ON o.OrderNo = od.OrderNo
    LEFT JOIN DelTicket d ON o.OrderNo = d.OrderNo
WHERE CONVERT(VARCHAR(MAX), o.User_Memo1) IS NOT NULL
    AND o.User_Date1 IS NULL
    AND DATEADD(d, -24, DATEADD(d, 7, CAST(SUBSTRING(o.User_Memo1, 
    CHARINDEX('Pack',o.User_Memo1)+5, 8) AS DATE))) < CAST(GETDATE() AS DATE)
    AND SUBSTRING(o.User_Memo1, 1, 3) = 'Cut'
GROUP BY o.OrderNo, DATEADD(d, 7, CAST(SUBSTRING(o.User_Memo1, 
CHARINDEX('Pack',o.User_Memo1)+5, 8) AS DATE)), o.User_Date1, o.User_Date2, 
o.User_Number2, d.ShipDate, o.User_Number3
ORDER BY DATEADD(d, 7, CAST(SUBSTRING(o.User_Memo1, 
CHARINDEX('Pack',o.User_Memo1)+5, 8) AS DATE)) DESC;

I get this error:

Conversion failed when converting date and/or time from character string.>

If I put this item:

    AND DATEADD(d, -24, DATEADD(d, 7, CAST(SUBSTRING(o.User_Memo1, 
CHARINDEX('Pack',o.User_Memo1)+5, 8) AS DATE))) < CAST(GETDATE() AS DATE)

At the end of the WHERE clause, it runs perfectly as expected. Items that don't have 'Cut' in User_Memo1 should indeed fail converting. However, I've read that the order doesn't matter. So I'm confused??

Edit: Updating with screenshots

enter image description here

enter image description here

Upvotes: 0

Views: 69

Answers (1)

Martin Karouš
Martin Karouš

Reputation: 153

In theory the order does not matter. However, it may happen with complex queries that query optimizer follows a different path and it results in a different execution plan. This is typical for queries with multiple joins. When you change the join order, the shape of the execution plan may change. I guess that this might by the same issue. Try to check the estimated execution plan for both options to see whether they vary...

When the you place the problematic predicate at the end of the WHERE clause. The execution plan is different (just Microsoft knows why) and the problematic rows that causes the error are filtered out before the conversion.

Upvotes: 1

Related Questions