Can I use an SQL query in an activity?

Question

question

Marco B8 asked Sep 17, '18 Marco B8 commented Sep 18, '18

Can I use an SQL query in an activity?

Good morning at all!

In my model I have a global table composed by a column that define the level of the rack and another column that define the coordinate z of the level.

In an activity I would like to find the level of the rack using the coordinate. I tried to use a cicle while and a cicle for connected to the global table, but when the token pass the second time from the activity, the result is completely wrong.

So I would like to use an sql query to find the row of the global table which have the values that I need, not using the various cicle. The z coordinate to search in saved in a variable.

My questions are: 1) is possible to write the code in an activity? 2) do you have any ideas how to set the code?

Thanks!

I attach my code. The activities involved in my research are the two "Travel_LiftToTier" in the right side of the model

10-vs3-mgneg.fsm

Software Version:

FlexSim 17.2.5

global table sql queries activity

10-vs3-mgneg.fsm (100.7 KiB)

Answer 1 · 2018-09-17T09:27:16Z

Raja Sekaran answered Sep 17, '18 Marco B8 commented Sep 18, '18

@Marco B8

I have reattached the model with the changes.

I updated the query to get row number of the table based on the z coordinate value.

Table result = Table.query("SELECT table.ROW_NUMBER FROM table WHERE z = $1",zsh1);
int rowNum = result[1][1];

getrownumber.fsm

Hope this helps.

Thanks

getrownumber.fsm (126.7 KiB)

· 8

Marco B8 commented · Sep 17, 2018 at 09:43 AM

Hi @Raja Sekaran!

Thanks for your idea, but it doesn't work in my model. I attach the error that I found.

error.png

0 ·

error.png (23.6 KiB)

Raja Sekaran Marco B8 commented · Sep 17, 2018 at 09:46 AM

Hi @Marco B8

It works well for me.

Does query return the value? Or it throws some exception.

0 ·

Marco B8 Raja Sekaran commented · Sep 17, 2018 at 09:48 AM

It returns to me this exeption:

time: 60.000000 exception: FlexScript exception: No resolved tables at MODEL:/Tools/ProcessFlow/ProcessFlow/Travel_LiftToTier~3>variables/involved1

0 ·

Show more comments

Marco B8 commented · Sep 17, 2018 at 09:54 AM

Pay attention only that in the "script" I have the code for create the objects.

10-vs3-mgneg-prova.fsm

Really thank you for your help!!!

0 ·

10-vs3-mgneg-prova.fsm (100.7 KiB)

Raja Sekaran Marco B8 commented · Sep 17, 2018 at 10:24 AM

@Marco B8

This exception already exists in the model, not because of the query which we updated recently. Currently, you have 6 "Travel_LiftToTier" activity in the model. In that, we have updated the query in the two of those. To find out the root cause, please update the name of the activity for those 6 "Travel_LiftToTier" like "Travel_LiftToTier1","Travel_LiftToTier2" etc.,

This will help us to identify which activity throws an exception.

0 ·

Marco B8 Raja Sekaran commented · Sep 17, 2018 at 11:18 AM

I changed the name as you suggest, but the exception remains:

time: 60.000000 exception: FlexScript exception: No resolved tables at MODEL:/Tools/ProcessFlow/ProcessFlow/3.1Travel_LiftToTier>variables/involved1

I attach the model with the modify to the name.

10-vs3-mgneg-prova.fsm

0 ·

10-vs3-mgneg-prova.fsm (100.6 KiB)

Show more comments

question