Acess a Variable in a Query in FlexScript

Question

question

Gesa R asked May 9, '18 Mischa Spelt commented May 11, '18

Acess a Variable in a Query in FlexScript

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.

Software Version:

FlexSim 18.0.3

global table flexscript query sql queries data

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 · 2018-05-09T14:30:05Z

Mischa Spelt answered May 9, '18 Mischa Spelt commented May 11, '18

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);

· 2

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.

Gesa R commented · May 10, 2018 at 08:55 AM

Thank you, that is what I was looking for.

0 ·

Mischa Spelt Gesa R commented · May 11, 2018 at 09:29 AM

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[i]["Customer Group"] != customerGroup) {
    // Customer group changed
    customerGroup = result[i]["Customer Group"];
  }


  // createobject(...)
}

0 ·

question