question

Thomas JR avatar image
0 Likes"
Thomas JR asked Ben Wilson commented

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.

FlexSim 20.2.3
flexsim 20.2.3sql queries
· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Ben Wilson avatar image Ben Wilson ♦♦ commented ·

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 Likes 0 ·
martin.j avatar image
0 Likes"
martin.j answered

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

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot avatar image
0 Likes"
Jason Lightfoot answered
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

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Lars Christian J2 avatar image
0 Likes"
Lars Christian J2 answered Lars Christian J2 edited

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

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.