How to do a SQL Query from the Arrivals Table ?

Question

question

Leah R asked Sep 30, '23 Felix Möhlmann answered Sep 30, '23

How to do a SQL Query from the Arrivals Table ?

Hello !

I want to create a user command that query the patient source arrival of my model.

I tried running this code in the script console:

// OPTION 1
// Table tableArrivals = Model.find("Tools/ProcessFlow/Arrivals/Source>variables/arrivals");
// Table table = Table.query("SELECT SUM(D_discProv_cleanup) FROM tableArrivals WHERE id_salle = 1 AND perf_caseRank > 3");

// OPTION 2
Table table = Table.query("SELECT SUM(D_discProv_cleanup) FROM TableArrivalsSaved WHERE id_salle = 1 AND perf_caseRank > 3");

// RESULT
double value = table[1][1];
return value;

Using the first two rows (option 1) resulted in the following error:

exception: FlexScript exception: No resolved tables at <no path> c: <no path> i: <no path>

Using the option 2 allowed me to obtain the expected value.

My questions are the following:

1) Is it necessary to clone the arrival table to query it?

2) Is there an easier way to get the sum value without passing by "table[1][1]" ? I tried returning the query directly and it does not work as it is in the form of a Table.

Thanks in advance,

Leah

Software Version:

FlexSim 23.2.1

query tables

1696078711477.png (344.1 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 · 2023-09-30T13:53:07Z

Felix Möhlmann answered Sep 30, '23

The only things you can query directly by name are, to my knowledge, global tables, global lists, statistisc collector tables, and calculated tables. You can however pass in any node that contains table data (either as a bundle or through subnodes) into the query as a parameter.

See the section "Table Selection" in the documentation.

You can also read the result directly without assigning it to a variable first.

Table tableArrivals = Model.find("Tools/ProcessFlow/Arrivals/Source>variables/arrivals");
return Table.query("SELECT SUM(D_discProv_cleanup) FROM $1 WHERE id_salle = 1 AND perf_caseRank > 3", tableArrivals)[1][1];

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.

question