Business One Order By Case

[former_member551008]

Business One 8.82 pl12

I have a query that the user wants the results back as they conducted the input the query looks as follows:

SELECT T0.ItemCode, T0.ItemName, T0.U_colornam, T0.U_jc, T0.PicturName

FROM OITM T0

WHERE T0.ItemCode =[%0] or

T0.ItemCode =[%1] or

T0.ItemCode =[%2] or

T0.ItemCode =[%3] or

T0.ItemCode =[%4] or

T0.ItemCode =[%5] or

T0.ItemCode =[%6] or

T0.ItemCode =[%7] or

T0.ItemCode =[%8] or

T0.ItemCode =[%9] or

T0.ItemCode =[%10] or

T0.ItemCode =[%11] or

T0.ItemCode =[%12] or

T0.ItemCode =[%13] or

T0.ItemCode =[%14]

this is the query that currently is running that does not provide the results in the order that they are put in. I looked at ordering by case which worked great in testing hard coded values. This is the way I want it to operate.

SELECT T0.ItemCode, T0.ItemName, T0.U_colornam, T0.U_jc, T0.PicturName FROM OITM T0 WHERE T0.ItemCode =[%0] or
T0.ItemCode =[%1] or
T0.ItemCode =[%2] or
T0.ItemCode =[%3] or
T0.ItemCode =[%4] or
T0.ItemCode =[%5] or
T0.ItemCode =[%6] or
T0.ItemCode =[%7] or
T0.ItemCode =[%8] or
T0.ItemCode =[%9] or
T0.ItemCode =[%10] or
T0.ItemCode =[%11] or
T0.ItemCode =[%12] or
T0.ItemCode =[%13] or
T0.ItemCode =[%14]
ORDER BY CASE
WHEN T0.ItemCode =[%0] THEN 1
WHEN T0.ItemCode =[%1] THEN 2
WHEN T0.ItemCode =[%2] THEN 3
WHEN T0.ItemCode =[%3] THEN 4
WHEN T0.ItemCode =[%4] THEN 5
WHEN T0.ItemCode =[%5] THEN 6
WHEN T0.ItemCode =[%6] THEN 7
WHEN T0.ItemCode =[%7] THEN 8
WHEN T0.ItemCode =[%8] THEN 9
WHEN T0.ItemCode =[%9] THEN 10
WHEN T0.ItemCode =[%10] THEN 11
WHEN T0.ItemCode =[%11] THEN 12
WHEN T0.ItemCode =[%12] THEN 13
WHEN T0.ItemCode =[%13] THEN 14
WHEN T0.ItemCode =[%14] THEN 15
END

I edited the query to read this way and it does not run.

I get incorrect syntax near keyword 'ORDER'.2

can anyone see or help me with the flaw of this code?

[former_member390407 · Accepted solution]

Hi Derek,

Please try the following:

SELECT T0.ItemCode, T0.ItemName, T0.U_colornam, T0.U_jc, T0.PicturName,
CASE 
WHEN T0.ItemCode =[%0] THEN 1
WHEN T0.ItemCode =[%1] THEN 2
WHEN T0.ItemCode =[%2] THEN 3
WHEN T0.ItemCode =[%3] THEN 4
WHEN T0.ItemCode =[%4] THEN 5
WHEN T0.ItemCode =[%5] THEN 6
WHEN T0.ItemCode =[%6] THEN 7
WHEN T0.ItemCode =[%7] THEN 8
WHEN T0.ItemCode =[%8] THEN 9
WHEN T0.ItemCode =[%9] THEN 10
WHEN T0.ItemCode =[%10] THEN 11
WHEN T0.ItemCode =[%11] THEN 12
WHEN T0.ItemCode =[%12] THEN 13
WHEN T0.ItemCode =[%13] THEN 14
WHEN T0.ItemCode =[%14] THEN 15
END AS [Order]
FROM OITM T0
WHERE T0.ItemCode =[%0] or
T0.ItemCode =[%1] or
T0.ItemCode =[%2] or
T0.ItemCode =[%3] or
T0.ItemCode =[%4] or
T0.ItemCode =[%5] or
T0.ItemCode =[%6] or
T0.ItemCode =[%7] or
T0.ItemCode =[%8] or
T0.ItemCode =[%9] or
T0.ItemCode =[%10] or
T0.ItemCode =[%11] or
T0.ItemCode =[%12] or
T0.ItemCode =[%13] or
T0.ItemCode ='[%14]'
ORDER BY 6

[former_member551008 · Accepted solution]

Very good thank you.

Regards,

-DM