ORDER BY ABS() in Storage system query?

Question

question

Mikael B asked May 4, '21 Mikael B commented May 5, '21

ORDER BY ABS() in Storage system query?

Hi, I wonder if its possible to ORDER BY absolute values in a storage system query. The code I have been trying to use is:

return Storage.system.findItem("WHERE SKU IS $1 AND Item.item.ExpiryDate IS $3 ORDER BY ABS($2-Item.slot.storageObject.zone) ASC", 0, token.SKU, Zone, expDate);

SKU is an indexed item label, ExpiryDate is an item label, but not indexed, zone is a label on storage racks.

This code gives these error messages:

Compiler Console:

Invalid sql function ABS

System Console:

exception: FlexScript exception: Invalid query int Storage.System.querySlots() at <no path> c: <no path> i: <no path>

I have also tried replacing ABS() with Math.fabs(), but this just returns different error messages.

Am I doing something wrong, or is there some other workaround to ORDER BY absolute values in a storage system query?

By some googling I know ORDER BY ABS() is supported in normal SQL queries.

The reason for trying to ORDER BY absolute values, is I want to find an item in the closest zone to $2 Zone. The zone label is an integer label with values from 1-18.

Software Version:

FlexSim 20.0.10

query flexsim 20.0.10 finditem order by

· 1

Joerg Vogel commented · May 04, 2021 at 09:52 AM

SQL Language Support

0 ·

Answer 1 · 2021-05-04T10:32:36Z

Lars Christian J2 answered May 4, '21 Mikael B commented May 5, '21

You have explicit tell FlexSim to evaluate it as FlexScript by using curly brackets

return Storage.system.findItem("WHERE SKU IS $1 AND Item.item.ExpiryDate IS $3 ORDER BY {Math.fabs($2-Item.slot.storageObject.zone)} ASC", 0, token.SKU, Zone, expDate);

From the User Manual:

FlexScript Expressions: you can use any FlexScript expression within an SQL expression. In some cases, however, you may need to escape the expression using curly braces. This is often required when using the square bracket [ ] in FlexScript, since it has different meaning in SQL than in FlexScript. This causes some errors parsing expressions like WHERE MyCol = Table("MyTable")[1][1]. In this case you can instead escape the FlexScript expression within the SQL using curly braces { }, as follows: WHERE MyCol = { Table("MyTable")[1][1] }.

· 1

Mikael B commented · May 05, 2021 at 03:14 PM

Thank you!

0 ·

question