List pull logic uses SQL queries to determine what should be pulled and how to prioritized it. This topic is a quick introduction to SQL, including several example queries.
This topic uses the connectionless routing example as the base model for query examples. If you would like to test these examples, build the model and then come back to this topic.
For information about the special values of value and Puller, see Functional Reference.
SQL Clauses
In SQL, you use what are called clauses to define queries. Each clause represents a rule by which the query should perform its search. There are many different clauses available in standard SQL, but when using lists, you usually only need to know 2, namely WHERE and ORDER BY.
WHERE Clause
The WHERE clause essentially defines what you want to pull from a list. When you pull from a list using a query with a WHERE clause, you are essentially saying, "I want to pull something from the list where the entry meets this criterion." For example, one scenario in the connectionless routing example uses the following query.
WHERE itemType = 2This WHERE clause defines a rule that you only want to pull items whose itemtype is equal to 2. More specifically, the itemTypefield value of the list entry must be equal to 2.
ORDER BY Clause
The ORDER BY clause essentially defines how to prioritize what should be pulled when there are multiple potential candidates for pulling. When you pull from a list using a query with an ORDER BY clause, you are essentially saying, "I want to pull something from the list, and if there are multiple possibilities, order them by this rule." For example, one scenario in the connectionless routing example uses the following query.
ORDER BY age ASCThis ORDER BY clause says that you want to prioritize the items on the list with the lowest age. More specifically, the pull query should sort candidates by the agefield value in ascending order, and pull the first item in the resulting sorted list. Here we use the optional ASC keyword to tell it to sort in ascending order (default). We could alternatively use the DESC keyword to sort in descending order.
Handling Ties
The ORDER BY clause can easily handle ties. Just comma-separate multiple expressions to define subsequent prioritization rules if there are ties.
ORDER BY itemType DESC, age ASCThis example says that you should first order by itemType in descending order, and then if there are ties (multiple items with the same itemType), then next order by age in ascending order.
Using Both WHERE and ORDER BY Clauses
If you define a query that uses both the WHERE and ORDER BY clauses, the WHERE should come first, and the ORDER BY can be placed immediately after it, as in the following example.WHERE itemType = 2 ORDER BY age ASC
This query says that it will only pull items with itemtype 2, and if there are multiple that meet that criterion, it should take the one with the smallest age.
Expression Operators
The following table shows operators that you can use in writing pull queries.
| Operator | Definition | Example |
|---|---|---|
| Comparison Operators | ||
| = or == | Equals comparison | WHERE itemType = 2 |
| < | Less-than comparison | WHERE itemType < 2 |
| > | Greater-than comparison | WHERE itemType > 2 |
| <= | Less-than-or-equal comparison | WHERE itemType <= 2 |
| >= | Greater-than-or-equal comparison | WHERE itemType >= 2 |
| <> or != | Not-equal comparison | WHERE itemType <> 2 |
| BETWEEN | Between a defined range (inclusive) | WHERE itemType BETWEEN 2 AND 5 |
| IN | Value in a defined set | WHERE itemType IN (1, 3, 5) |
| Logical Operators | ||
| AND or && | Logical and | WHERE itemType = 2 AND queueSize < 5 |
| OR or || | Logical or | WHERE itemType = 2 OR queueSize < 5 |
| Math Operators | ||
| + | Addition | ORDER BY distance + age |
| - | Subtraction | ORDER BY distance - age |
| * | Multiplication | ORDER BY distance * age |
| / | Division | ORDER BY distance / age |
| Expression Grouping | ||
| () | Grouping Parentheses | WHERE (itemType < 2 OR itemType > 8) AND queueSize < 5 |
Examples
The following examples are based on the connectionless routing example. Again, if you would like to test these examples out, build the model then come back to this topic.
Multi-Rule Prioritization
If you combined scenario 1 with scenario 2, you will likely have noticed that the queues would often accumulate with itemtypes 1 and 3 more than itemtype 2. This is because one of the processors was dedicated to itemtype 2, while the others would take any of the items. Hence, itemtype 2 happened to be pulled more often because both the dedicated processor and the other processors were pulling it, to the detriment of the other itemtypes.
Let's say that we need to keep the same requirements, namely one dedicated processor with the rest undedicated, but we want to try to better alleviate the "unfair" queueing of itemtypes 1 and 3. One option is for the undedicated processors to prioritize itemtypes 1 and 3. Only if there are neither of these itemtypes available would the undedicated processors pull itemtype 2. Nevertheless, we still want to pull in LIFO order when there are multiple possibilities within the first division.
To implement this logic in the model, define the pull query on the undedicated processors as follows:
ORDER BY itemType <> 2 DESC, age ASCThis example defines itemType <> 2 DESC as the highest order priority, and age ASC as the second order priority. Here we use the rule that a logical expression that is true will give the value 1, whereas a logical expression that is false will give the value 0. So, the expression itemType <> 2 gives a 1 when itemType is not equal to 2, and will thus take priority over 0 (itemType is equal to 2) in a descending order sort. The second order priority takes effect if there are ties in the first, namely if it found multiple items of itemtype 1 or 3, or if it couldn't find any 1's or 3's, but it found multiple 2's. In those cases it will sort that valid set by age in ascending order, effecting a LIFO behavior.
Dedicated Processor, Multiple Types
You may have a processor that is dedicated to more that one itemtype. For example, one processor only processes itemtypes 1 and 3. This can be acheived with either of the following pull queries.
WHERE itemType = 1 OR itemType = 3WHERE itemType IN (1, 3)FlexScript Functions
You can also use FlexScript function calls in your queries. This is often used to do math operations that are not in the standard set. For example, you may want to sort primarily by distance (find the item closest to where the pulling processor currently is), but only up to some threshold, and then sort by some other rule. Specifically, maybe you want to separate distance into "buckets" of 10 meters each. Items within the same "bucket" would be pulled in LIFO order (age ASC). Here you would use the round() function to round the distance to every 10 meters, and then use age as the second order priority.
ORDER BY round(distance / 10) ASC, age ASCSELECT Clause
In some special circumstances you might also use the SELECT clause. See the Functional Reference for more information.
