Tip of The day
Evaluation order
/*
Accounts:
account_nbr account_type account_reference
----------- --------------- -----------------
1 Personal abc
2 Business Basic 101
3 Personal def
4 Business Plus 5
*/
SELECT account_nbr, account_reference AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%'
AND CAST(account_reference AS INT) > 20;
SELECT account_nbr, account_ref_nbr
FROM (SELECT account_nbr,
CAST(account_reference AS INT) AS account_ref_nbr
FROMAccounts
WHEREaccount_type LIKE'Business%') AS A
WHERE account_ref_nbr > 20;
Evaluation order (solution)
SELECT account_nbr, account_reference AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%'
AND CASE WHEN account_reference NOTLIKE '%[^0-9]%'
THEN CAST(account_reference AS INT)
END> 20;