Dynamics AX Nested Query

Question

Maybe I'm missing something simple, but is there a way to write a nested query in AX? I tried some syntax I thought would work, but with no luck.

The following standard SQL statement would accomplish what I'm trying to do, but I need to do this in AX, not SQL.

SELECT table1.column1A, table1.column1B, 
    (SELECT Top 1 column2B FROM table2 
        WHERE table1.column1A = table2.column2A 
        ORDER BY table2.column1A) 
    AS lookupResult 
FROM table1

My problem is that table1 has a one-to-many relationship with table2, and since AX doesn't have a DISTINCT function that I'm aware of, I receive many copies of each record when using a JOIN statement.

Thanks

Answer 1

Nested queries are not supported in AX.

One way to bypass the missing distinct is to use group by (assuming max value of column2B is interesting):

while select column1A, column1B from table1
    group column1A, column1B
    join max-of(column2B) from table2
    where table2.column2A == table1.column1A     
{
    ...
}

Another method would be use a display method on table1 in the form or report.

display ColumnB column2B()
{    
    return (select max-of(column2B) from table2
                where table2.column2A == this.column1A).column2A;
}

The performance is inferior to the first solution, but it may be acceptable.

Answer 2

As mentioned in the previous reply, group-by is the closest you can get to a distinct function. If you need a simpler query for some reason, or if you need a table or query object to use as a datasource on a form or report, you may entertain the idea of creating a view in the AOT, which contains the group-by. You can then use that view to easily join to on a query object or form datasource etc...

Answer 3

Ax2012 has support of computed columns in views, you can use the SysComputedColumn class to build query you want