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

(0) Share

Report

Posted on by Community Member

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

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Bashir Ahmad 5,248 on at

Like (0)

Report

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}

Was this reply helpful? Yes No
Suggested answer

5400 7,162 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

jasman 1,413 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

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.

Was this reply helpful? Yes No