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
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.
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.
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}
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.
Oh sorry, I misread that as two times ProdId. I never saw something like this. Maybe you can split it up into two selects?
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.
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?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,607
Super User 2024 Season 2
#2
Martin Dráb
228,847
Most Valuable Professional
#3
nmaenpaa
101,150
Share
Report
All responses (
Answers (