Problem with getquerymatchtablerow()

Question

question

Sebastian Hemmann asked May 24, '16 Brandon Peterson edited Jun 17, '16

Problem with getquerymatchtablerow()

The attached example model shows a user-command ("command1") with this query:

query("SELECT MAX(Prio) FROM GlobalTable1");

The greatest value of the column "Prio" is searched in GlobalTable1 and this works fine.

But getquerymatchtablerow("GlobalTable1", 1); does not return the row 1 where the value is in row 1.

I can´t find out why but it seems as the number of rows in GlobalTable1 is returned?

Software Version:

FlexSim 16.0.1

flexsim commands global table query user command scripting

beispiel.fsm (12.9 KiB)

Answer 1 · 2016-06-14T17:51:44Z

Phil BoBo answered Jun 14, '16 Phil BoBo edited Jun 14, '16

It turns our that the command getquerymatchtablerow() is working as designed. We will update the documentation to explain this more clearly.

Because you have an aggregation function (MAX), the query can't determine what row you are asking about. For example, if you executed the query below, it would find values from two different rows:

query("SELECT MAX(Prio), MIN(Nr) FROM GlobalTable1");

A better way to handle this query is:

query("SELECT Prio FROM GlobalTable1 ORDER BY Prio DESC LIMIT 1");
return getquerymatchtablerow(1,1);

Answer 2 · 2016-05-24T15:53:35Z

Phil BoBo answered May 24, '16 Phil BoBo edited May 24, '16

This looks like a bug with that command. I'll add a note to the development list.

For now, you could store the row number in a column (like you are in this example) and return that value from the query:

query("SELECT Nr, Prio FROM GlobalTable1 ORDER BY Prio DESC LIMIT 1");

Attached is a sample model showing this method.

getquerymatchcount-workaround.fsm (14.1 KiB)

question