BAQ result rows missing while UNION and UNION ALL

Kinetic ERP
1

I have a Business Activity Query (BAQ) with 5 UNION ALL subqueries. When I run each subquery individually, all rows are displayed. However, when I use UNION ALL to combine them, one row is missing from the result set. I noticed that this issue occurs when I merge two specific subqueries, and despite thorough investigation, I couldn’t identify any errors. but found a result row with similar value in one column, remaining all coloumns have diffrent values.

  • I tried by removing all null values using formula fields, no change

  • couldnt trace any DataType miss Matches
    Since we are on cloud I couldnt test it on Ms SQL.

Am I making a mistake, or has anyone encountered a similar issue? I would greatly appreciate your assistance. I have attached the Business Activity Query (BAQ) for reference.
SupplierTran.baq (66.6 KB)

2

All of the subqueries must have the same number of fields and the same type of fields. Otherwise the union can’t proceed. You have the wrong matching in your subqueries. Take a look at the Query Builder > Display Fields > Column Select. Anywhere you see #ERR?# in the Subquery Set Data field, you have to fix those fields. Those columns show you how each field is matched, so make sure all your fields are the same type and you should get a good result.

3

He said “Row” not “Column” @NateS

4

I don’t see anything immediately obvious. I can’t really run it, as the setup you have is specific to your company.

5

Try to add Group by for all columns and check.

6

Season 3 Mistake GIF by The Simpsons

7

I have identical data types and fields, and while the BAQ is successfully retrieving results, it is excluding one specific row from the output. If I run Subqueries seperately I can see the missed row.

8

There are aggregate values present, so it may not be ideal to group them.

9

There are no company-specific filters or conditions only From & To date parameters, It will work if you have AP transactions. All standard links applied, and I’ve utilized UnionAll subqueries with TranGLC to APtran, Chekhed, APInvHed, and GLjrndtl. While executing the query, I adjusted the result limit to 12,000 since I had over 10,000 results. surprising fact is that it displays the missing result when I execute it with two months’ date