question

Craig DIckson avatar image
1 Like"
Craig DIckson asked Jordan Johnson commented

Query a table in PF, using local variable or label as criteria?

Hi-

I am doing a query in a process flow activity, and I need to use a couple of labels on the token as criteria in the query. I tried a few methods, and they all either returned 0 or gave SQL errors.

First I tried using "puller. " as one might do in a "Pull from list" activity:

  1. query("SELECT SKU, CurrentDemand FROM Status_SKUs WHERE CurrentDemand > 0 AND PICK_MODULE = puller.labelStorageLocation AND BAY = puller.labelCurrentBay ORDER BY BAY ASC LIMIT 1" );

This gave a SQL error e.g. "time: 4.712563 exception: Invalid SqlQuery object". I replaced puller.labelxx with getlabel(puller.xx and also with getlabel(token.xx and got the same result.

Then I tried using $1, $2 etc. to make the label references:

  1. query("SELECT SKU, CurrentDemand FROM Status_SKUs WHERE CurrentDemand > 0 AND PICK_MODULE = $2 AND BAY = $1 ORDER BY BAY ASC LIMIT 1" 
  2.  ,getlabel(token,"labelCurrentBay"), getlabel(token,"labelStorageLocation"));

This didn't give SQL errors but it just returned 0.

Additional background that might help you understand what I need:

I was actually able to get what I wanted using a findmax() command. But it is very very slow (code profiler shows that activity taking 20 times more time than the next slowest activity) and my hope is that query() will be faster.

Here's the findmax that did what I wanted:

  1. iSKUtoPick = findmax(gettablenum("Inputs_SKUs_UniqueSKUs",1,1), // Search all SKUS
  2. gettablenum("Status_SKUs", count, CURRENT_DEMAND) // For maximum current demand
  3. * (iAisle == gettablenum("Status_SKUs", count, PICK_MODULE)) // But only in current aisle
  4. * (iCurrentBay == gettablenum("Status_SKUs", count, BAY)), // And bay
  5. count, //Return the SKU # (since currently it equals the row #)
  6. (gettablenum("Status_SKUs", count, CURRENT_DEMAND) >0) ); // But return 0 if there is no demand

Thanks-

Craig

FlexSim 16.0.4
querysql
5 |100000

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

1 Answer

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Jordan Johnson commented Best Answer

In your case, the second query (using the $1, $2) looks correct. It appears you are looking for the emptiest bay that the token is allowed to use. When the query command returns 0, it means that no rows matched the query.

There could be several reasons for this. Are you using global macros in your queries? Because that is a known issue, and it doesn't usually work. Use the name of the table column instead. For example, if PICK_MODULE is a global macro, then it should be replaced with the name of its corresponding column. The same might be true for BAY.

Another possibility is that the value in the BAY column is not the same type as the value on the current bay label of the token. For example, labelCurrentBay might store the location of the object, rather than a bay number.

As far as speed goes, the query command might be faster, but it might not. Is the Inputs_SKUs_UniqueSKUs table a bundle? If so, you can index the PICK_MODULE and BAY columns, which will make the query command much faster than findmax. To do this, make sure the Use Bundle checkbox is checked (which will delete your row headers if you had them). Then, right click the column you want to index, and choose Optimize Column for Lookup. Do this for both the PICK_MODULE and BAY columns.

· 4
5 |100000

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