Order by not working in joins x++

(1) Share

Report

Posted on by Community Member

Hello Everyone,

I am trying to use "order by" clause in the below code. but the "order by" is not working. Can any one please help how to use the "order by" in the below code, or how can i achieve the "order by" functionality in the below stated code.

Thankyou in advance.

insert_recordset recSelect(field1, field2, field3)
            select forceNestedLoop RecId, Id from A
                where A.ID== "4"
            join firstonly RecId from B

              order by Num desc
                where B.RefTableId == A.TableId
                   && B.RefRecId   == A.RecId
                   && B.Type       == 3

Bests

Shah

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Community Member on at

Like (0)

Report

Hi Crispin,

Thankyou for your reply, I tired it as well but it did not worked.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

Please try with below statement.Also, you can loop through only your select statement(using while-select) to check the behavior.

insert_recordset recSelect(field1, field2, field3)

select forceNestedLoop RecId, Id from A

join firstonly RecId from B

order by Num desc

where B.RefTableId == A.TableId

&& A.ID== "4"

&& B.RefRecId == A.RecId

&& B.Type == 3

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

Something tells me that the FirstOnly in the sub-join does nothing. I'm surprised it doesn't throw an error. There is no equivalent in T-SQL.

I'm also suspicious that the ForceNestedLoop is effectively forcing table A to be the "inner" table, and you're getting "forced order" behavior because of the LOOP hint.

Inspect the actual query being generated and its cache plan and I think you'll understand why you're not getting the results you think you should.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

As Brandon has suggested, check the actual query executed in T-SQL. Likely the AX kernel ignores some of the keywords you are trying to use specific to the insert_recordset statement.

You could try to check it with the generateOnly keyword and getSQLStatement() if you make it a regular select, then compare it against the generated query when using insert_recordset to spot any differences.

Was this reply helpful? Yes No