SQL query COUNT and MAX

Question

question

shanice.c asked Sep 9, '21 Joerg Vogel commented Sep 10, '21

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

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.

Software Version:

FlexSim 21.2.0

flexsim 21.2.0 sql queries

sql-query.png (60.4 KiB)

1631150380973.png (30.8 KiB)

5 |100000

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

Answer 1 · 2021-09-09T06:54:02Z

Felix Möhlmann answered Sep 9, '21 shanice.c commented Sep 10, '21

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

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

shanice.c commented · Sep 10, 2021 at 04:15 AM

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?

0 ·

1631247104734.png (55.8 KiB)

Joerg Vogel shanice.c commented · Sep 10, 2021 at 04:32 AM

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 ·

question