Why Does Ignition Named Query UNION Data Shift Rows?

Read the Dataset Before You Touch the Label

Here is the symptom. The operator scans the first part of an 8-part stack, and its serial shows in the label bound to row 1. The operator scans the second part. The first serial jumps to row 2. On the third scan it moves to row 3. The labels bound by row index now show the wrong part, or zero.

The query behind it is a named query with eight SELECT COALESCE(SUM(serial), 0) FROM parts WHERE ... AND stack_pos = n statements joined with UNION. It is bound to a view custom property, and each label reads a fixed cell such as Dataset[1,0].

Start here. Open the custom property in the Perspective property editor, or run the named query in the Designer test pane with real :layer, :layer_pos and :pallet values. Count the rows before and after each scan.

That is not a Perspective binding fault. The label is doing exactly what you told it to do. The dataset shape is changing under it.

Check 1: Is the Query Using UNION or UNION ALL?

UNION is a set operation. It returns distinct rows only. Before the stack is full, every empty position returns the same single value, 0. Seven identical 0 rows become one row. The dataset shrinks, and every row index shifts.

Many database engines remove duplicates by sorting or hashing the combined result. A sort puts 0 ahead of any real serial, and the real serials follow in value order, not in stack_pos order. On each new scan, one zero row turns into a real value, the row count changes, and existing serials slide down one index. That is the "pushed down" behavior.

• If the query uses UNION: switch to UNION ALL. That stops the collapse, but you still have no guaranteed order. Go to Check 2.
• If it already uses UNION ALL and rows still move: the order is the problem. Go to Check 2.

Check 2: Does the Result Carry Its Own Key and an ORDER BY?

SQL does not guarantee row order without ORDER BY. Not for UNION ALL, not for a plain SELECT. Code that works today can reorder after an index change, a statistics update, or a database upgrade.

Look at the columns. If the dataset has only a serial column, no ordering is possible because nothing in the data says which row belongs to position 1.

Minimum fix, if you have to keep the eight-select structure: add the position as a literal column and sort on it.

SELECT 1 AS stack_pos, COALESCE(SUM(serial), 0) AS serial
  FROM parts
 WHERE layer = :layer AND layer_pos = :layer_pos
   AND pallet = :pallet AND stack_pos = 1
UNION ALL
SELECT 2, COALESCE(SUM(serial), 0)
  FROM parts
 WHERE layer = :layer AND layer_pos = :layer_pos
   AND pallet = :pallet AND stack_pos = 2
-- ... repeat through 8 ...
ORDER BY stack_pos

Each branch always returns exactly one row, because an aggregate with no GROUP BY returns one row even when nothing matches. With UNION ALL and ORDER BY, you get eight rows in position order. It works, but it runs eight scans of parts per poll. Go to Check 3 for the clean version.

Check 3: Do Empty Positions Return a Row on Their Own?

The clean design uses one query that returns one row per position, whether a part exists or not. To do that, you need a source of all eight positions. The source is a small table, for example stack_positions, with one column holding 1 through 8. Left-join parts to it.

SELECT sp.stack_pos,
       COALESCE(SUM(p.serial), 0) AS serial
  FROM stack_positions sp
  LEFT JOIN parts p
    ON p.stack_pos = sp.stack_pos
   AND p.layer     = :layer
   AND p.layer_pos = :layer_pos
   AND p.pallet    = :pallet
 GROUP BY sp.stack_pos
 ORDER BY sp.stack_pos

Put the :layer, :layer_pos and :pallet filters in the ON clause. If you move them to WHERE, they discard the null rows from unmatched positions, and the left join acts as an inner join. The empty positions disappear again, and so does your fixed row count.

Then stop reading by row index. Bind each label to the value, keyed by position, using lookup():

lookup({view.custom.stackData}, 3, 0, "stack_pos", "serial")

Arguments: the dataset, the value to find (position 3), the value to return if there is no match (0), the column to search, and the column to return. Now the label shows position 3 even if the row order changes, rows are missing, or a ninth position is added later. This is the resolving branch.

Apply the Fix and Verify It

1. Create the stack_positions table and insert values 1 through 8. Match the data type of parts.stack_pos so the join does not need an implicit cast.
2. Replace the named query body with the left-join query above. Keep the three parameters and their types.
3. In the Designer test pane, run it against a pallet that has no scans. Expect exactly 8 rows, stack_pos 1 through 8, all serial = 0.
4. Run it against a partly scanned pallet. Expect 8 rows, with serials only in the positions that were scanned.
5. Bind the named query to one view custom property, for example view.custom.stackData, with the poll rate you already use.
6. Replace every per-label query binding with an expression binding that uses lookup() and that label's position number.
7. Delete the old per-label named query bindings. If you leave them in place, they keep polling.

Verify on the floor:

• Scan parts out of order (for example 5, then 1, then 8). Each serial must appear under its own label and stay there.
• Delete or re-position a part using the screen's existing functions. Only the affected labels change on the next poll.
• Check the gateway's database connection status page. Active connections and query throughput should drop.

Load, derived from the numbers given: 25-30 screens with about 10 one-second queries each is 250-300 queries per second when every screen is open. One query per screen brings that to 25-30 per second, a tenfold reduction before you change any poll rate.

Pitfalls That Waste Time

• Adding ORDER BY without a key column. You can only sort on data. Sorting by serial does not give position order.
• Swapping UNION for UNION ALL and stopping. The collapse stops, but the order still is not guaranteed. It will break again later.
• SUM(serial) hides double scans. If the same position is scanned twice, the label shows the sum of two serials, which is a meaningless number. If one part per position is the rule, use MAX, or return COUNT(*) as a second column and flag any count above 1.
• Filters in WHERE on a left join. This is covered in Check 3, and it is the most common reason the fixed version still returns fewer than 8 rows.
• Row-index bindings elsewhere. Search the view for other [row,col] references to the same property and convert them to lookup().
• Rewriting everything into session memory first. Buffering scans in custom properties and writing at the end of the stack is a valid design. But when the screen also deletes and re-positions parts directly in the database, the keyed query is the smaller change.

FAQ