Finance | Project Operations, Human Resources, ...

Query order by complex expression

(4) Share

Report

Posted on by FV@E

133

Hello fellow Dynamics users,

I hope you can help me building a query in Dynamics with a complex order by clause.

The query that I want to build (simplified version):

select * from SomeTable order by     case         when Field1 = Value1 then x        when Field2 = Value2 then y        else z    end

Now I haven't found a way of doing this with the QueryBuildDataSource class. The addOrderByCalculationField method sounds like a good start, but so far I haven't been able to find any documentation on how to use that. The function takes a Microsoft.Dynamics.AX.Analytics.CalculationModel.NumericExpression parameter, but that is an abstract class. And I haven't found any list of possible inheriting classes online.

Anyone who can point me in the right direction?

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Anthony Blake 2,926 Super User 2025 Season 2 on at

Like (0)

Report

In your scenario you could have 3 queries, one covering each order by:

Query 1: use Field1=Value1as criteria

Query 2: use Field2=Value2 as criteria

Query 3: use Field1 != Value1 && Field2 != Value2 as criteria

There could be repeated results as Field1 and Field2 could have overlapping results, but that's also an issue with your original query/problem.

Was this reply helpful? Yes No
Suggested answer

Mohamed Amine Mahmoudi 26,390 Super User 2025 Season 2 on at

Like (0)

Report

Hi @FV@E,

I guess you can create a view with the result of case.

than you can order by your new field based the result.

Best regards,

Mohamed Amine MAHMOUDI

Was this reply helpful? Yes No
Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Can you write what you want to achieve using SQL statement? Do you want to have order by have cases?

Thanks,

Waed Ayyad

Was this reply helpful? Yes No
FV@E 133 on at

Like (0)

Report

Thank you all for your suggestions. What I'm trying to achieve is to create a slightly more complex ordering than simply ordering by 1 field. For example: I want my query to have 2 outer joins with other tables. Records where the first outer join matches should have the highest priority, records where the second outer join matches should have the second priority etc.

I can build my query easily in SQL, that's not the problem. My problem is how to convert this syntax to x++;

@Anthony: Yes I agree I could solve it by using multiple queries. However, due to performance I would prefer to do it in 1 query. Especially since I already know it can be done in SQL. I'm just not sure if I can generate the correct SQL from x++.

@Mohammed Thank you for your suggestion, but I don't think I can capture this logic in a view

Was this reply helpful? Yes No