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

Question

question

Craig DIckson asked Sep 12, '16 Jordan Johnson commented Sep 15, '16

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:

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:

query("SELECT SKU, CurrentDemand FROM Status_SKUs WHERE CurrentDemand > 0 AND PICK_MODULE = $2 AND BAY = $1 ORDER BY BAY ASC LIMIT 1" 
 ,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:

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

Thanks-

Craig

Software Version:

FlexSim 16.0.4

query sql

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 · 2016-09-13T21:24:02Z

Jordan Johnson answered Sep 13, '16 Jordan Johnson commented Sep 15, '16

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

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

Craig DIckson commented · Sep 13, 2016 at 11:51 PM

@jordan.johnson

Jordan,

Yes, PICK_MODULE and BAY global macros(actually they were both macros and column names (d'oh) ), so changing the column names and then using those did seem to make it work.

You suggested making it the table a bundle to speed things up, but also mentioned that that would eliminate the column names. How would I reference the columns in that case?

Thanks

Craig

1 ·

Jordan Johnson ♦♦ Craig DIckson commented · Sep 15, 2016 at 05:43 PM

My mistake. Making it a bundle would eliminate row names, not column names.

1 ·

Craig DIckson commented · Sep 15, 2016 at 09:33 PM

@jordan.johnson Oops - you did say it right, I just read it wrong.

I did try this, but I do not see "Optimize Column for Lookup" in the right-click menu.Is that perhaps a version issue (I am using 16.0.4)

Thanks,

Craig

0 ·

Jordan Johnson ♦♦ Craig DIckson commented · Sep 15, 2016 at 11:41 PM

I think that feature was added in 16.1. Sorry about that!

0 ·

question