Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Conditionnal order by clauses in x++ sql select statement?

Posted on by Microsoft Employee

Is there any way to put conditional statements in an order by?

I'd like to do something like this:

order by prod.ProdPrio desc, (prod.ProjId == "" ? prod.ProdId : prod.SchedStart) asc


  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    What you'd like to do isn't possible in SQL. The ORDER BY is done over a resultset not for each row. That makes prod.ProjId == "" irrelevant.

    What is possible is to put an 'CASE Prod.ProjId = "" THEN Prod.ProdId ELSE Prod.SchedStart END AS fieldName' in the select clause and use 'fieldName ASC' in the ORDER BY clause.

    Doing this in X++ is not possible. but what you could do is create a view with a view method that contains the 'CASE Prod.ProjId = "" THEN Prod.ProdId ELSE Prod.SchedStart END'. Use this viewmethod in a new field. You can query the view in X++ as if it is a table now.

  • Suggested answer
    jasman Profile Picture
    jasman 1,411 on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    You can not tell the database to alter the sorting order while it is running the query.

    But maybe you could do two queries:

    One with

       where prod.ProjId == ""

       order by prod.ProdPrio desc, prod.ProdId

    And one with

       where prod.ProjId != ""

       order by prod.ProdPrio desc, prod.SchedStart asc

    And then make another query doing a union on the two first queries.

    Then you could apply sorting to the union query.

  • Suggested answer
    5400 Profile Picture
    5400 7,160 on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    In single select statement, you can not put two type of sort with different field.

    you can achieve through querybuilddatasource  and ordermode function with  OR condition.

  • Suggested answer
    Bashir Ahmad Profile Picture
    Bashir Ahmad 5,248 on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    you need two select commands one with sort by date and one with sort by prodID

    place a if condition if (project attached)

    { Query sort by date} ELSE {Query sort by ProdId}

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    If I slit it up, I'd need to re-order them afterwards... and my queryr needs them to be ordered because I exit the while when a certain amount of lines are reached.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    Oh sorry, I misread that as two times ProdId. I never saw something like this. Maybe you can split it up into two selects?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    In fact, the condition is on ProJid, while I want to sort by ProDid.

    So, if there is a project attached, I want to sort by date, but if there is no project id I want to sort by ProdId.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Conditionnal order by clauses in x++ sql select statement?

    You can put the fields in order and it then will be applied on the select in the order you specified the fields

    order by ProdPrio desc, ProdId asc, SchedStart asc

    This would first sort by ProdId and then SchedStart. So for those records where ProdId is the same (which includes empty), it will be sorted by SchedStart. I don't think it will be an issue if the records which do have a ProdId will also be sorted by SchedStart?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans