How to iterate columns in Table.query

Question

question

Thomas JR asked Nov 23, '20 Ben Wilson commented Dec 1, '20

How to iterate columns in Table.query

I have a global table named My_table with column names Ressource_1, Ressource_2 and so on. The table values contain numbers between -1 and 10.

I want to find the minimum value of each column. I have tried something like this:

for (int j = 1; j <= My_table.numCols; j++) {
    string res = My_table.getColHeader(j);
    int min_value = Table.query("SELECT MIN($1) as Dummy FROM My_table WHERE $1 <> -1", getlabel($iter(1), res))[1][1]
}

As you can see the column IS the parameter itself. In addition, I also need the WHERE statement to filter out values of -1.

I've had a look in the documentation

SQL Queries (flexsim.com)

but I'm unable to pinpoint the syntax for my case. FlexSim simply reports that it can't parse the query.

Software Version:

FlexSim 20.2.3

flexsim 20.2.3 sql queries

· 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.

Ben Wilson ♦♦ commented · Dec 01, 2020 at 03:16 PM

Hi @Thomas JR, was jason.lightfoot's or martin.j's or Lars Christian J2's answer helpful? If so, please click the red "Accept" button on one of their answers. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept or comment back to reopen your question.

0 ·

Answer 1 · 2020-11-23T12:48:49Z

martin.j answered Nov 23, '20

Hi Thomas,

I dont know if you can actually use the $iter function to iterate through table columns, since there is no way to return a specific column form a table. But if you are going to use a loop to iterate through your results anyway you could simply parse a query string with the right name. This has the disadvantage of creating a query for each iteration though:

int lowest = 999999999;
Array results = Array(My_table.numCols);
for (int i = 1; i <= My_table.numCols; i++) { 
string columnName = My_table.getColHeader(i); 
string qry = "SELECT MIN(" + columnName + ") As Dummy FROM $1 WHERE " + columnName + " <> -1"; 
int min = Table.query(qry, My_table)[1][1];  
results[i] = min; 
if (min<lowest) 
 lowest = min;
}

In this example the results array will will contain the lowest value for each corresponding column, and the integer will contain the lowest overall value found. You could also use the ROW_NUMBER syntax to find out in what row the lowest value was.

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 2 · 2020-11-23T14:19:57Z

Jason Lightfoot answered Nov 23, '20

Another option is to change your table so that the records (rows) are resources and then the SQL statements become much simpler and more powerful since you're using standard database designs.

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 3 · 2020-11-23T11:45:09Z

Lars Jacobsen answered Nov 23, '20 Lars Jacobsen edited Nov 23, '20

If you add a semicolon to the end of your sql statement, there will not be a parse error. However, the statement will still not work as expected. Currently you cannot use $ to replace column names for a table in a select statement.

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