How to iterate columns in Table.query

[thomas_jr]

[ FlexSim 20.2.3 ]

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.

[lars_jacobsen_ScandiSim]

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.

[MBJEBZSRG · Accepted solution]

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

[jason_lightfootVL7B4]

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.

[Ben_WilsonADSK]

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.