question

shanice.c avatar image
0 Likes"
shanice.c asked Joerg Vogel commented

SQL query COUNT and MAX

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)?

sql-query.png

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.

1631150380973.png

FlexSim 21.2.0
flexsim 21.2.0sql queries
sql-query.png (60.4 KiB)
1631150380973.png (30.8 KiB)
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

1 Answer

·
Felix Möhlmann avatar image
0 Likes"
Felix Möhlmann answered shanice.c commented

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];
· 2
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

shanice.c avatar image shanice.c commented ·

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?

1631247104734.png

0 Likes 0 ·
1631247104734.png (55.8 KiB)
Joerg Vogel avatar image Joerg Vogel shanice.c commented ·

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#Property-numRows

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.