--CELKO-- wrote:

> 1) ISNULL() is dialect and needs to be replaced with ANSI/ISO Standard
> COALESCE(). This is minor (but they actually are a little different),
> but it tells anyone maintaining your code that they can expect a lot
> of dialect and not much current SQL.

Out of curiosity, is there any functional difference other than
COALESCE() not being limited to two arguments?

Also, is there a particular reason to wrap (S, CC1, CC2) in
parentheses, rather than just do the following (which keeps
each join condition adjacent to its corresponding join)?

FROM Tbl_0 AS X
INNER JOIN Tbl_1 AS S ON X.aml_rec_id = S.aml_rec_id
LEFT OUTER JOIN CC AS CC1 ON S.something_country1 = CC1.something_code
LEFT OUTER JOIN CC AS CC2 ON S.something_country2 = CC2.something_code

Also, it might be clearer to flatten the nested CASEs into one level:

COALESCE(CASE WHEN CC2.ofc = 0 THEN 0
WHEN CC2.ofc = 1 AND CC1.tpc > 130 THEN 10
WHEN CC2.ofc = 1 AND CC1.tpc <> 0 THEN 5
ELSE 0 END),0) AS magic_nameless_integer