question

Craig DIckson avatar image
2 Likes"
Craig DIckson asked Sam Stubbs commented

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:

  1. // See if there is a line for this SKU on this side of the sorter
  2. query("SELECT labelOrderNum FROM listLinesWaitingToBePicked AS LinesToChoose, listMLO_OrdersOnSorter AS OrdersToChoose \
  3. WHERE LinesToChoose.labelSKU = $1 \
  4. AND LinesToChoose.labelOrderNum = OrdersToChoose.labelOrderNum \
  5. AND OrdersToChoose.labelEvenOdd = $2 \
  6. 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

FlexSim 16.0.5
querylistssql
· 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.

1 Answer

Jordan Johnson avatar image
2 Likes"
Jordan Johnson answered Sam Stubbs commented

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:

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

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

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