Another Query question, this time regarding querying lists

Question

question

Craig DIckson asked Sep 14, '16 Sam Stubbs commented Sep 21, '16

Another Query question, this time regarding querying lists

Thanks to @jordan.johnson 's answer yesterday, I got my table query working.

Today though I am working on another section of my model, and I need to query some lists of tokens:

// See if there is a line for this SKU on this side of the sorter
query("SELECT labelOrderNum FROM listLinesWaitingToBePicked AS LinesToChoose, listMLO_OrdersOnSorter AS OrdersToChoose \
WHERE LinesToChoose.labelSKU = $1 \
AND LinesToChoose.labelOrderNum = OrdersToChoose.labelOrderNum \
AND OrdersToChoose.labelEvenOdd = $2 \
ORDER BY labelOrderNum ASC LIMIT 1", getlabel(token,"labelSKUJustPicked"), getlabel(token,"labelEvenOdd"));

I tried it with the lists as local, and again using global lists, and in both cases I made fields for the labels being used in the query. (The code is in a Custom Logic activity.) But I always get this error:

time: 429.349220 exception: Exception caught in flexscript execution of MODEL:/Tools/ProcessFlow/ProcessFlow/Try to find a requirement on this side of sorter>variables/codeNode line 13 instruction 13. Discontinuing execution. time: 429.349220 exception: Exception caught in TreeNode::callMemberFunction() flexscript execution MODEL:/Tools/ProcessFlow/ProcessFlow/Try to find a requirement on this side of sorter>variables/codeNode c: /Tools/ProcessFlow/ProcessFlow/Try to find a requirement on this side of sorter>variables/codeNode i: /Tools/ProcessFlow/ProcessFlow/Try to find a requirement on this side of sorter>variables/codeNode theclass: /Tools/ProcessFlow/ProcessFlow/Try to find a requirement on this side of sorter>variables/codeNode

Any suggestions?

Thanks for your help-

Craig

Software Version:

FlexSim 16.0.5

query lists sql

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

Craig DIckson commented · Sep 15, 2016 at 03:00 AM

ETA I did some experiments, going back to the most simple possible query:

query("SELECT * FROM listLinesWaitingToBePicked");

Even this returns the error, so it looks like the query() statement doesn't work to search lists, only for tables? If that is the case, I'll need to do some significant restructuring to my model because I really don't want to search a table of 80K rows when I only have a thousand or so active at a time.

1 ·

Answer 1 · 2016-09-15T17:07:51Z

Jordan Johnson answered Sep 15, '16 Sam Stubbs commented Sep 21, '16

The query command is set up to work on lists. However, the SELECT * statement does not work. You must specify which fields you want particularly. I think your query is failing because both lists appear to have a labelOrderNum field. That could be causing the SQL parse to fail, because it doesn't know which field to use. Check your compiler console to see if SQL parse is succeeding. If that is the issue, you should specify which list the field comes from using syntax like LinesToChoose.labelOrderNum.

This query could probably use a JOIN clause in it. You have that you want only entries WHERE LinesToChoose.labelOrderNum = OrdersToChoose.labelOrderNum, which is really a JOIN. Once you join the tables on the correct column, it removes the ambiguity. So I suggest the following query instead:

query(
"SELECT labelOrderNum\
FROM listLinesWaitingToBePicked JOIN listMLO_OrdersOnSorter ON labelOrderNum\
WHERE labelSKU = $1 AND labelEvenOdd = $2\
ORDER BY labelOrderNum ASC LIMIT 1",
getlabel(token,"labelSKUJustPicked"),
getlabel(token,"labelEvenOdd")
);

I have assumed that the only common field between the two lists is the labelOrderNum. If that's not true, the WHERE statement will need more qualifications.

Without your model, I can't guarantee that this query works, but the concepts may still be helpful.

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

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

@anthony.johnson

I tried some variations on what you suggested and still no luck. Sometimes it gave parse errors in the Compiler console; sometimes it gave long, cryptic errors in the System console (as in the example above)

I have found a workaround by creating a dynamic field on the first list - but that is of course slow since it reevaluates every token in the list every time. (Per code profiler, ~10 times slower than any other activity). So i'd still like to figure it out.

What's the best way to get you a copy of my model so you can see what's actually happening?

Thanks,

Craig

0 ·

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

If you can, I would post it on Answers. If you mark the question as private, then it will not be searchable by the public, or by regular FlexSim users.

0 ·

Craig DIckson commented · Sep 16, 2016 at 12:12 AM

@jordan.johnson

Jordan,

Thanks. Maybe that's the reason I need to upgrade. I've been being cautious, so I don't accidentally break anything.

Next time I will look for the private tag next time.

Thanks for the help.Craig

0 ·

question