How to use SQL to find an available rack slot within a specific bay range

Question

question

Jon Abbott asked Dec 16, '19 Jon Abbott commented Dec 16, '19

How to use SQL to find an available rack slot within a specific bay range

I am using a SQL query similar to below to find an available rack slot in a specific rack:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' ORDER BY RAND()",0)

How can I adjust this to only consider a specific range of bays within that rack? For example, assume Rack1 has 30 bays. I would like to have the SQL query only consider bays 5-10 of Rack1 for returning an available slot, and I would like to use SQL to accomplish this. Thanks in advance for any help you can provide.

Software Version:

FlexSim 20.0.0

sql sql query rack storage slot bays

5 |100000

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

Answer 1 · 2019-12-16T21:09:11Z

anthony.johnson answered Dec 16, '19 Jon Abbott commented Dec 16, '19

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND slot.bayID BETWEEN 5 AND 10 ORDER BY RAND()",0)

When you use the "slot." prefix, it assumes you are accessing the slot as a Storage.Slot FlexScript object, so you can access any of the members available in that interface. From the manual:

"If the slot's storage object has an Address Scheme, the slot's bay ID is formatted according to that address scheme. In other words, if the address scheme's bays are addressed with a letter, bayID will be a string, otherwise bayID will be a number. If there is no address scheme, bayID will be the bay number."

I assume you are not using an address scheme, or if you are, the bays are defined by numbers. Thus, in your case you can assume slot.bayID will give you back a number, so you can use BETWEEN in SQL.

The other, more verbose, way to access the rank of the bay would be:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND slot.bay.as(treenode).rank BETWEEN 5 AND 10 ORDER BY RAND()",0)

This will directly access the rank of the bay, which may be useful if you expressly don't want to use bayID, e.g. if you've set up an address scheme that doesn't give you ranks.

· 1

5 |100000

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

Jon Abbott commented · Dec 16, 2019 at 09:51 PM

Great, thanks for explaining how this works, @anthony.johnson. This behaves exactly how I would expect. I had consulted the manual but hadn't discovered the Storage.Slot page yet. Your explanation helps to tie it all together.

0 ·

Answer 2 · 2019-12-16T19:34:45Z

tannerp answered Dec 16, '19 Jordan Johnson commented Dec 16, '19

Hi @Jon Abbott,

I think you can narrow your search using a BETWEEN statement in the code you have. Example:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND bayNumber BETWEEN 5 AND 10 ORDER BY RAND()",0)

You would just need find how to access the appropriate column that represents the bay number.

· 3

5 |100000

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

Jon Abbott commented · Dec 16, 2019 at 07:47 PM

Hi @tanner.p, thank you for the quick response. I tried this but it returns <no path>, which isn't the behavior I was expecting. I'm not using a table to represent the rack, so there is no column for bayNumber to refer to. Normally I can run the findSlot command using the Script Console and it returns a node like this:

Node: Rack1>variables/bays/29/levels/2/slots/1

I'm looking for a way to write the SQL query in a way that doesn't use a variable but instead uses the rack's own storageObject properties so that it returns a node like above but limited to a specific bay range.

0 ·

tannerp Jon Abbott commented · Dec 16, 2019 at 08:03 PM

@Jon Abbott

Bummer that didn't work. It seemed like the easiest thing to do.

I asked a colleague for an alternative suggestion and he thinks it might be easiest to use labels on the slots. This way, if you have certain slots that you want to pick from, you can still use SQL, but you'll look for slots with a specific label. I think this should work as long as you know beforehand which slots are go and which slots are no-go.

0 ·

Jon Abbott tannerp commented · Dec 16, 2019 at 08:17 PM

Thanks @tanner.p. It seems redundant to add labels to each slot that indicate which bay it is, when the bay number is already a property of the slot... in the documentation, there is an entry for Storage.Object.bays... is there a way to reference this using SQL?

0 ·

question