topic Can I use an SQL query in an activity? in FlexSim Forum

Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 06:59:53 GMT

[ FlexSim 17.2.5 ]

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

Re: Can I use an SQL query in an activity?

raja_sekaran — Mon, 17 Sep 2018 09:27:16 GMT

@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

Re: Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 09:43:24 GMT

Hi @Raja Sekaran!

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

error.png

Re: Can I use an SQL query in an activity?

raja_sekaran — Mon, 17 Sep 2018 09:46:21 GMT

Hi @Marco B8

It works well for me.

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

Re: Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 09:48:15 GMT

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

Re: Can I use an SQL query in an activity?

raja_sekaran — Mon, 17 Sep 2018 09:50:08 GMT

@Marco B8

Could you attach the model?

Re: Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 09:54:09 GMT

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!!!

Re: Can I use an SQL query in an activity?

raja_sekaran — Mon, 17 Sep 2018 10:24:26 GMT

@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.

Re: Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 11:18:19 GMT

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

Re: Can I use an SQL query in an activity?

marco_b8 — Mon, 17 Sep 2018 11:35:06 GMT

In the global table I haven't numbered the rows: could be this a problem?