SQL table query

Question

question

Maria Z3 asked Nov 01 2019 at 12:21 PM tannerp commented Dec 04 2019 at 10:49 PM

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

Software Version:

FlexSim 19.2.1

sql query table table queries

sql-question.fsm (18.8 KiB)

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2019-11-04T15:26:11Z

Jordan Johnson answered Nov 04 2019 at 3:26 PM Kyle P commented Nov 19 2019 at 6:27 PM

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

Maria Z3 commented · Nov 12 2019 at 1:41 PM

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 ·

Joerg Vogel Maria Z3 commented · Nov 12 2019 at 2:07 PM

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

0 ·

Maria Z3 commented · Nov 12 2019 at 2:44 PM

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

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

0 ·

sql-question-v2.fsm (27.0 KiB)

Kyle P Maria Z3 commented · Nov 19 2019 at 6:27 PM

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 ·

question

SQL table query

1 Answer

Things to know…

question details

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.

question

SQL table query

1 Answer

Things to know…

question details

Related Questions

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

THIRD PARTY SERVICES

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.