question

Maria Z3 avatar image
0 Likes"
Maria Z3 asked tannerp commented

SQL table query

Dear all,

I am rather new in Flexsim. Therefore, hopefully the question will be easy for you. I am trying to create a code for SQL query for a global table. I have created a very simple example and attached.

Basically, I am trying to retrieve 2 columns ( in my table and prioritize these columns based on the certain requirement. The table in real model is significantly longer than in the file attached.

I wanted to store the results in a dump table to see whether my logic is correct. I also want to assign a label to my token with the result of one value retrieved from table.

1. I am getting an error and cannot understand what I am doing wrong

2. I cannot see the results in a Dump Table

I would deeply appreciate, if you could help me or give a hint. sql-question.fsm

FlexSim 19.2.1
sql querytabletable queries
sql-question.fsm (18.8 KiB)
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Kyle P commented

There are a couple issues with spacing in the query. Be sure that when you use the \ character, that you leave a space before it or on the next line after it. That character just continues the text to the next line, but does not add any white space. You also need to add spacing in your SELECT statement:

// good 
SELECT * FROM Parameters \

// not good
SELECT*FROM Parameters\

But beyond that, it looks like you are trying to filter by values in a certain row. Queries operate on columns rather than rows. If you want to use syntax like

WHERE C > 5

then C needs to be a column.

Finally, while it would be really convenient to use token directly in your query, you won't be able to in this case. You will need to pass it in as a parameter, and use a placeholder for that parameter in your query:

Table.query("... WHERE C > $1.Item.size ...", token)
· 4
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Maria Z3 avatar image Maria Z3 commented ·

Thank you very much for your help, dear @jordan.johnson. I must confess, it did not help me to solve the problem.

I have solved the problem by writing a Custom code with for (int i = 1; i <= 4; i++) etc. It worked, however, the code looks quite complex and not elegant.

I am trying to model the logic in a Process Flow, avoiding an elaborate Custom Code. For that, I have created a subflow, where one token will be split into 4 tokens, corresponding to each column with parameters A,B,C (as in the example file). Before subflow I give reference indication, which A, B, C to use. Then, I push the tokens to the list and pull them.

At the moment, I cannot figure out, why my query does not work in a Pull from List.

I wrote the following, trying to select only one token(!) from the List that fulfills requirement A< ProductID and has a minimal value B:

SELECT A < ProductID ORDER BY B ASC LIMIT 1

At the moment, it pulls everything that A < ProductID without any limit.

What do you think, I am doing wrong? How can I limit to only one token?

0 Likes 0 ·
Joerg Vogel avatar image Joerg Vogel Maria Z3 commented ·

if you attach your model, it is much easier to identify what is going wrong.

0 Likes 0 ·
Maria Z3 avatar image Maria Z3 commented ·

Dear @Jörg Vogel, I have attached the file

Probably MIN() function can be used as well. Do not work either.

0 Likes 0 ·
sql-question-v2.fsm (27.0 KiB)
Kyle P avatar image Kyle P Maria Z3 commented ·

Not sure if you figured this out, but I could not find anything to reference a "ProductID", I changed it to "OrderID" and the model did run. I can't tell if it ran correctly though.

1 Like 1 ·