question

Jon Abbott avatar image
1 Like"
Jon Abbott asked Jon Abbott commented

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:

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

FlexSim 20.0.0
sqlsql queryrack storageslotbays
5 |100000

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

anthony.johnson avatar image
4 Likes"
anthony.johnson answered Jon Abbott commented
  1. 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:

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

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

tannerp avatar image
0 Likes"
tannerp answered Jordan Johnson commented

Hi @Jon Abbott,

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

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

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