How different are the top DB vendor's SQL (DML-) dialects from each other? Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from each other. Since I come from an Oracle background, I am especially interested in


analytical functions
model clauses
hierachical queries (start with .. connect by comes to mind)
any other feature that is more than the usual (select x, y from a, b where...)


Probably, the question boils down to if and to what extent these features are regulated by an ansi standard.

Practically, I'd like to know if there are "rule of thumbs" that would indicate if (and how) I can take an SQL DML-statement that runs on one database and let it run on an other database.
ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything.  Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL.  Of course as each company seeks to differentiate their products, they will develop additional functionality beyond the standards.
From w3schools:

Although SQL is an ANSI (American
National Standards Institute)
standard, there are many different
versions of the SQL language.
However,
to be compliant with the ANSI
standard, they all support at least
the major commands (such as SELECT,
UPDATE, DELETE, INSERT, WHERE) in a
similar manner.
Note: Most of the SQL
database programs also have their own
proprietary extensions in addition to
the SQL standard!

The differences between the SQL dialects is too great in the more specialized areas you listed to simplify the problem down to a “rule of thumb”.  In these situations you probably should convert the entire statement for each database.  By doing this each statement can be optimized for each platform taking advantage of the special features each database can provide.  Writing SQL to lowest common denominator (ANSI) may simplify migration, but at the cost of performance.
SQL Dialects Reference gives some useful comparisons of the SQL variants.  For more information I recommend searching for the specific dialects you are switching between like this:
   differences between mysql and oracle sql