topic Re: How to query an Experiment.SC ? in FlexSim Forum

How to query an Experiment.SC ?

nicolas_membrez4BM5L — Tue, 07 Nov 2023 14:06:19 GMT

[ FlexSim 23.2.2 ]

How to passe an Experiment.StatisticCollector table as a parameter in a query ?

Table FromTable   = ??? 

/*1)*/ Table Query = Table.query("SELECT * FROM Experiment.SC");
/*2)*/ Table Query = Table.query("SELECT * FROM $1",FromTable);
Query.cloneTo(TableResult);

Re: How to query an Experiment.SC ?

jason_lightfoot_adsk — Tue, 07 Nov 2023 16:14:34 GMT

Something like this will work:

Table.query("SELECT * FROM Experiment.[Staytime Collector]").cloneTo(Table("GlobalTable1"))

You can just construct the string using the stats collector name - you don't need it as a parameter:

Object sc=Model.find("Tools/StatisticsCollectors/Staytime Collector");
Table.query("SELECT * FROM Experiment.["+ sc.name+"]").cloneTo(Table("GlobalTable1"));

You may want to join the taskid on the tasks and scenarios tables to get more useful fields.

Re: How to query an Experiment.SC ?

JordanLJohnson — Tue, 07 Nov 2023 16:24:38 GMT

Option one is the correct way to query an experiment table:

/*1)*/ Table Query = Table.query("SELECT * FROM Experiment.SC");

It is very common that the table name needs brackets:

Table Query = Table.query("SELECT * FROM Experiment.[SC]");

It is usually unnecessary to pass the table as a parameter, as FlexSim supports nested queries:

SELECT ... FROM (SELECT a, b, c FROM Experiment.SC WHERE ...)

If you really need to query a custom data structure (which could include a table), you can use placeholders and $iter():

Table fromTable = Table("GlobalTable1");
Table result = Table.query("SELECT $2 AS Col1, $3 AS Col2 FROM $1"
    , fromTable.numRows         /*pass in the number of rows in your table*/
    , fromTable[$iter(1)][1]    /*use $iter(1) to access the nth row*/
    , fromTable[$iter(1)][2]
);
result.cloneTo(Table("GlobalTable2"));

Re: How to query an Experiment.SC ?

nicolas_membrez4BM5L — Tue, 07 Nov 2023 17:39:33 GMT

It was exactly my need, thanks a lot

Re: How to query an Experiment.SC ?

nicolas_membrez4BM5L — Tue, 07 Nov 2023 17:40:21 GMT

Thanks a lot for the explanations ! I have to study this !