"$1" not working in SQL table query

Question

question

Craig DIckson asked Jul 26, '19 Joseph Gillespie commented Aug 2, '19

"$1" not working in SQL table query

We have a model that is using Table.query() to find data in a global table. We need to limit the number of results, so the SQL statement uses a LIMIT clause in the SQL. It works fine when the limit is hardcoded in the SQL ("... LIMIT 18") , but FlexSim can't parse it if we use $1 and a variable (so we can vary the number desired in the search). The code is:

string sQuery = "SELECT INDEX, OBJECT, BOXES_QUEUED, STATE
FROM table_boxes_queued WHERE BOXES_QUEUED > 0 and INDEX < 28 ORDER BY
BOXES_QUEUED DESC LIMIT $1";

Table table_busiest_doors1 = Table.query(sQuery, NUM_OPERATORS);

When the code runs (2 minutes in to the run), it gives this run time error:

time: 120.000000 exception: FlexScript exception: Could not parse query
SELECT INDEX, OBJECT, BOXES_QUEUED, STATE FROM table_boxes_queued WHERE
BOXES_QUEUED > 0 and INDEX < 28 ORDER BY BOXES_QUEUED DESC LIMIT $1 at
MODEL:/Tools/ProcessFlow/ProcessFlow/Update Operator
Locations>variables/codeNode

where NUM_OPERATORS is a global macro with a value of 18. Note that it also does not work if you replace NUM_OPERATORS with a number either.

Any idea what I'm forgetting here?

Thanks,

Craig

Software Version:

FlexSim 19.1.1

sql queries table queries sql limit

Answer 1 · 2019-07-28T12:10:48Z

Joerg Vogel answered Jul 28, '19 Joerg Vogel edited Jul 28, '19

I confirm your issue, but it exists within the LIMIT clause.

You can try to build the query string without a lambda parameter for LIMIT similar to this code line:

string qt ="SELECT id, it FROM startdd WHERE it > $1 LIMIT "+string.fromNum(tt);

"tt" is a local integer variable

question