Customers with no Transactions in X++

The reason i am posting this is because i think this is an X++ query limitation in AX 2009....Let's start with my requirement:
In my AX environment, CustTable is associated with a Virtual company and the virtual company is associated with all the real companies. CustTrans is per company and my requirement is to find the Customers with no transaction at all. The X++ query that i came up with is (simple):

while select crosscompany _CustTable
   notexists join _CustTrans
   where _CustTable.AccountNum == _CustTrans.AccountNum
       {
        i++;
        // some process
       }

the way AX interprets this query is :
(Customers with no transactions in company A)  Union  (Customers with no transactions in company B) Union ........
this doesn't solve my requirement because a customer who doesn't have transactions in company A may have transactions in other companies.

There is no simple and efficient way to put this in X++  query so i used direct SQL query in X++ which came out to be very efficient in my case.Here is the SQL query i used:

SELECT A.ACCOUNTNUM,A.NAME
   FROM CUSTTABLE A
   WHERE NOT EXISTS
    (SELECT 'x' FROM CUSTTRANS B WHERE (A.ACCOUNTNUM=B.ACCOUNTNUM))


Here is the approach i used in X++:

void ExportCustomersWithNoTrans()
{
   System.IO.StreamWriter sw;
   InteropPermission permIO = new InteropPermission(InteropKind::ClrInterop);
   Connection dbConnection;
   Statement  sqlStatement;
   str        sqlStr;
   ResultSet  resultSet;
   SqlStatementExecutePermission permSQL;
   int counter;
   CustTable custTable;
   CustAccount currCustAccountNum;
    ;

    info(strFmt('Start Time for CustomersWithNoTrans - %1', DateTimeUtil::getSystemDateTime() ) );

    dbConnection = new Connection();
    sqlStr = 'SELECT A.AccountNum FROM CUSTTABLE A ';
    sqlStr += 'Where NOT EXISTS ';
    sqlStr += '(SELECT \'x\' FROM CUSTTRANS B WHERE  (A.ACCOUNTNUM=B.ACCOUNTNUM)) ';

    permSQL = new SqlStatementExecutePermission(sqlStr);
    permSQL.assert();
    sqlStatement = dbConnection.createStatement();
    resultSet = sqlStatement.executeQuery(sqlStr);
    CodeAccessPermission::revertAssert();

    permIO.assert();
    sw = new System.IO.StreamWriter(@"C:\Data\realTemp\CustomersWithNoTrans.txt");

    while(resultSet.next() )
    {
      currCustAccountNum = resultSet.getString(1);
      custTable = null;
      select custTable where custTable.AccountNum == currCustAccountNum;

      sw.WriteLine( strFmt('%1 \, %2', custTable.AccountNum , custTable.Name) );
    }

    sw.Flush();
    sw.Close();
    sw.Dispose();

    CodeAccessPermission::revertAssert();
    info(strFmt('End Time for CustomersWithNoTrans - %1', DateTimeUtil::getSystemDateTime() ) );

}

So, looks like the combination of direct SQL and X++ query is the way to go if  we come across X++ limitations or performance issues.

Hope you find this post to be useful. Happy DAXing......