ax 2012 - direct SQL querying alternatives

Question

I am using a direct SQL query as a data source of my SSRS report:

SELECT 
    emp.EmployeeNumber, FirstName, LastName, MiddleName
    , amts.AmountCode, amts.amt, amts.AmountType
from EmployeeMaster emp
LEFT OUTER JOIN 
(
    SELECT 'Earning' as AmountType, EmployeeNumber, EarningCode as AmountCode
            , SUM(Amount) AS amt 
            FROM EmployeeEarnings GROUP BY EmployeeNumber, EarningCode
    UNION ALL
    SELECT 'Deduction', EmployeeID, DeductionID
            , SUM(Amount) AS amt FROM EmployeeDeduction GROUP BY EmployeeID, DeductionId
) AS amts
ON emp.EmployeeNumber = amts.EmployeeNumber

This query gathers all the earnings and deductions per employee grouped by employee number, type of earning and type of deduction. I am unsure of whether I could do this in x++ or an AOT query. I am wondering if it is alright to do this, or is there be an alternative to produce a result like this query does? I am using RDP class.

Answer 1

Nested joins are not supported in AX. However you can make a union view of EmployeeEarnings and EmployeeDeduction. Then you can select EmployeeMaster and outer join with the view to get the same result.

Your SQL is incorrect as it does not consider dataAreaId and Partition.

Answer 2

Yes, Nested joins are not supported in AX. Yes, you can make a view. And you can use a ResultSet class to get result of the sqlStatement with a direct-SQL-query via SQLconnection.

Some time ago I'd created a wrapper class https://github.com/mazzy-ax/SysResultSet. Comments on Russian only. Sorry. Project to import into ax2012 https://github.com/mazzy-ax/SysResultSet/blob/ax2012/AOT/Classes/Class_SysResultSet.xpo

It simplifies the work with a direct-SQL-query. Try it if you know what you are doing.

And, yes, Your SQL is incorrect as it does not consider dataAreaId and Partition. In additional your SQL query ignore Axapta XDS (record level security) and Axapta security subsystem. An AOT query is more adequate I think.