topic Re: Acess a Variable in a Query in FlexScript in FlexSim Forum

Acess a Variable in a Query in FlexScript

gesa_r — Wed, 09 May 2018 14:04:46 GMT

[ FlexSim 18.0.3 ]

Hi,

I have much information in global tabels (from Excel). For my simulation logic I need access to these data and sometimes sort it.

When I have FlexScript and make a query to create a new temporary table, can I include a variable like "i" or a token lable in the query?

Example:

for (int i = 1; i<=10; i++)
{
	Table result=Table.query("SELECT * FROM Customers \
		WHERE [Customer Group] = i) ;


	//Create items in a special queue dependant of i 
}

How can I access the data in an other way?

Thank you for helping.

Re: Acess a Variable in a Query in FlexScript

mischa_spelt — Wed, 09 May 2018 14:30:05 GMT

Table result = Table.query("SELECT * FROM Customers \    
    WHERE [Customer Group] = $1", i) ;

The syntax $n references the nth additional parameter to .query(). You can use it in the FROM, WHERE and ORDER clause, e.g. the above could also be rewritten as

Table result = Table.query("SELECT * FROM $1 \
    WHERE [Customer Group] = $2", Table("Customers"), i);

Re: Acess a Variable in a Query in FlexScript

gesa_r — Thu, 10 May 2018 08:55:20 GMT

Thank you, that is what I was looking for.

Re: Acess a Variable in a Query in FlexScript

mischa_spelt — Fri, 11 May 2018 09:29:02 GMT

By the way, depending on how your table is setup, indexing, etc. it may be faster to just query all the rows with customer groups 1 through 10 in a single query, sorted by Customer Group, and have a single for loop over the result.

Table result = Table.query("SELECT * FROM Customers WHERE [Customer Group] >= 1 AND [Customer Group] <= 10 ORDER BY [Customer Group] ASC");
int customerGroup = 0;
for(int i = 1; i <= result.numRows; i++) {
  if(result["Customer Group"] != customerGroup) {
    // Customer group changed
    customerGroup = result["Customer Group"];
  }


  // createobject(...)
}