SQL query COUNT and MAX

[shanice_c]

[ FlexSim 21.2.0 ]

I would like to use the following code to look for one task for AGV, tempAGV value may be different, how could I refer to different AGVID in SQL Query.

Also, I use integer to read getTaskID, does it has to be written "Table.query()" in the right hand side of sign of equality to get MAX(TaskID)?

I also would like to check if AGV has tasks to do by sql query. I have read through some examples in the forum, they used query to write out sql query result in another table, and use .numRows to count how many tasks agv has. But I hope to get the tasks AGV has directly.

[moehlmann_fe · Accepted solution]

To pass a parameter into a sql query use the "$" syntax. In the query string, replace the value with "$N" where N is the number of the parameter (1, 2, ...) and append those parameters after the query string, separated by commas.

Example:

Table.query("SELECT * FROM TaskTable WHERE AGVID = $1 AND Done = 0", tempAGV)

The query always returns a table. If you are using COUNT(), MAX() or similar functions, the result will be in the first table cell.

int Value = Table.query("SELECT COUNT(*) FROM TaskTable WHERE AGVID = $1 AND Done = 0", tempAGV)[1][1];

[shanice_c]

Hi @Felix Möhlmann what if I want to get the value from the query result. As the query always returns a table, so I write it like below? And got exception: FlexScript exception: Invalid row number: 1.

How could I get the value I need from the query result?

[joerg_vogel_HsH]

This error tells you, there is not a result table. Please check in an if-statement, if the table has got at least a row.

For example by table class property: https://docs.flexsim.com/en/21.1/Reference/CodingInFlexSim/FlexScriptAPIReference/Data/Table.html#Pr...