Is it possible to deconstruct an array in the Table.query?

Question

question

Edgar Almeida asked Sep 22, '21 Jacob Gillespie edited Sep 22, '21

Is it possible to deconstruct an array in the Table.query?

Hello,

I was trying to do an query on a GlobalTable and my params are data into an array. The number of parameters are variable, so there is a way of "descontruct" the array into the params?
Ex:

Array arr = ['par1', 'par2', 'par3'];
Table result = Table.query("SELECT * FROM GlobalTable1 WHERE Col1 = $1 AND Col2 = $2 AND Col3 = $3", arr);

In the example above, 'par1' should enter in $1, 'par2' in $2 and so on.

Software Version:

FlexSim 21.2.2

query array sql queries

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 · 2021-09-22T18:43:32Z

Jacob Gillespie answered Sep 22, '21 Jacob Gillespie edited Sep 22, '21

It could be done like this:

Array arr = ["par1", "par2", "par3"];
string queryString = "SELECT * FROM GlobalTable1";
// build variable length query
if (arr.length > 0) {
    queryString += " WHERE ";
    for(int i = 1; i <= arr.length; i++) {
        queryString += "[Col " + i + "] = $1[" + i + "]";
        if (i != arr.length) {
            queryString += " AND ";
        }
    }
}
// makes a string like this "SELECT * FROM GlobalTable1 WHERE [Col 1] = $1[1] AND [Col 2] = $1[2] AND [Col 3] = $1[3]"
Table result = Table.query(queryString, arr);

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