Please can someone help me write my QUERY?

Question

question

Guillaume V asked Jul 21 2021 at 8:59 AM Guillaume V commented Jul 21 2021 at 2:52 PM

Please can someone help me write my QUERY?

Please help me, I am stuck...
I want to pull x2 tokens only if they have the same label "TEST" value in the list.
I do not care what value it is, just the fact that they have the same label value means that I want to pull them.

Any idea? I tried the function "COUNT()"but it didn't seem to be recognised by the Pull From List activity.

Software Version:

FlexSim 19.0.0

process flow query sql pull pullfromlist

1626857968547.png (165.7 KiB)

Answer 1 · 2021-07-21T11:16:24Z

Felix Möhlmann answered Jul 21 2021 at 11:16 AM Guillaume V commented Jul 21 2021 at 2:52 PM

I don't know if you achieve this in the pull activity alone. I can offer a workaround though.

First, instead of using an internal list I created a global list for the entries so I can query it with "Table.query()". In the Assign Labels activity I search for labels that appear more than once on the list. If successful, that value gets returned and assigned to the "LabelVar" label on the token. If not, a null value gets returned.

// Query for labels with more than one entry
Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > 1");
 
if(Identicals.numRows > 0)
{
    // If any are found return the first one
    return Identicals[1][1];
}
 
// Else return null
return nullvar;

Based on wether the label contains a valid value, the token then either waits for another value to be pushed to the list or continues on to the pull activity where it pulls the values based on the "LabelVar" label.

testnblabeltestidenticalinlist_1.fsm

pullwhensamelabel.png (49.1 KiB)

testnblabeltestidenticalinlist-1.fsm (41.1 KiB)

· 6

Guillaume V commented · Jul 21 2021 at 11:23 AM

Thank you so much @Felix Möhlmann it works perfectly fine. I learnt so much thanks to this, have a great day.

0 ·

Guillaume V commented · Jul 21 2021 at 11:34 AM

@Felix Möhlmann 1 last question, please.

I am not used to writing custom codes and it scares me a little.

I would like to change this line:

Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > 1");

and instead of testing if it is > 1, I would like to test if it is > a label value in token.
example:
Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > token.number");

I get an error when I write it like that.
How would you recommend writing the custom code, please Felix?

0 ·

Felix Möhlmann Guillaume V commented · Jul 21 2021 at 12:00 PM

You have to pass in the value as a parameter to the query command, like this:

Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > $1", token.number)

0 ·

Guillaume V Felix Möhlmann commented · Jul 21 2021 at 1:00 PM

Thanks @Felix Möhlmann

One very very last question:

I tried playing a little bit with the parameters. I would need to use a parameter as well for the Column Name (here LABELTEST).
I tried writing the Query like this:

Table.query("SELECT $1 FROM List1 GROUP BY $1 HAVING COUNT($1) = $2", token.QN, token.Q);

But when I do this, the returned variable stored in the "LabelVar" becomes the column name. I am very confused...

The problem seems to be the $1 = token.QN but I don't understand why.
I checked and in my model the value stored in the label QN of the tokens does correspond to the Column Name in the List1. I'm so confused...

0 ·

Show more comments

question