How to create complex SQL statements in Ax

I posted a reply to a forum discussion the other day, but somehow the post never showed up in the discussion. So I'm dedicating this blog post to the issue.

Ax comes with it's own set of supported SQL commands. You can do your basic select statements, sorting, grouping, counting etc
But there are things with SQL you cannot do in Ax. Like this

select SUM ( A * B) from myTable

Of course, you can create a while select statement, performing the sum record by record. But this comes with a performance penalty.

There is an alternative. Ax allows you to connect to the database, writing your own SQL statements.
We need to setup a new database connection, assign permissions and perform our own custom SQL statement.

Example:

server static void TestSumSelect()

{   Connection      connection;
Statement       statement;

str             sql;
ResultSet       resultSet;

SqlStatementExecutePermission perm;
;

connection = new Connection();
sql = strfmt( "SELECT SUM(QtyOrdered * SalesPrice) FROM SALESLINE where SalesId='xyz'");

perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql); 

while(resultSet.next())
{
info(num2str(resultSet.getReal(1),0,2,1,0));
}

CodeAccessPermission::revertAssert();
}

In order for this to work, the code must be executed on the server. (Encapsulate this in a class of your own.)
You can supply additional parameters (a salesid for example).