What is Epicor BAQ Union All?

Definition

Epicor BAQ Union All is a query operation used to combine the rows returned by two or more compatible query result sets into one output, retaining duplicate rows. In an Epicor Business Activity Query (BAQ), this approach can bring together records from different sources or query branches that share a common reporting structure. Unlike a distinct union operation, Union All does not automatically remove duplicate records.

For example, a finance analyst may need to report transactions from two operational datasets in one view. If both datasets return fields such as transaction date, document number, account, and amount in compatible formats, Union All can append the rows into a consolidated result. This helps create a single reporting dataset for review and analysis.

Table of Contents

How Epicor BAQ Union All Works

Union All combines result sets vertically: the rows from one query are placed alongside the rows from another in the same output structure. It does not join records side by side based on a matching key. The participating query branches therefore need compatible column structures, with corresponding fields representing the same kind of information.

The design process starts by identifying the datasets that need to appear together. The designer selects the required fields in a consistent order, aligns their meanings and data types, and configures the union operation using the options supported by the Epicor BAQ environment. The combined result can then be used for reporting, filtering, sorting, or further query logic where supported.

When working with Epicor ERP, confirm the exact BAQ designer capabilities and syntax available in your version. Query configuration options can vary by release and implementation, so test the resulting dataset in the target environment before using it for operational reporting.

When to Use Union All

Union All is useful when separate sources contain similar records that need to appear in one report, even if some values occur more than once. It is particularly helpful when the goal is to preserve every source row for reconciliation, audit review, or transaction analysis.

  • Transaction reporting: Combine compatible transaction rows from separate query branches into one dataset for review.
  • Period-based analysis: Append records from different periods when the reporting structure remains consistent.
  • Operational consolidation: Present similar activity from multiple business areas in a shared report.
  • Reconciliation: Retain source rows so analysts can compare record counts and investigate repeated transaction identifiers.

Suppose one query returns 120 invoice records and another returns 85 records. If both result sets are appended with Union All, the combined output contains 205 rows, assuming all rows are returned and no later filtering changes the result. If 10 rows represent the same underlying transactions in both datasets, those repeated rows remain in the output. The analyst should determine whether the repetition is expected or requires additional handling.

Union All Compared with Joins and Distinct Unions

Union All and joins solve different reporting needs. A join combines columns from related records, such as a supplier record with its invoice details. Union All appends rows from compatible result sets, such as two groups of invoice transactions. Choose the operation based on whether the report needs to expand each row with related fields or consolidate multiple row sets.

A distinct union operation combines compatible result sets while removing duplicate result rows according to its set-operation rules. Union All retains them. That difference matters when repeated records represent legitimate separate transactions, recurring activity, or overlapping source data. Removing duplicates without checking their meaning can change totals and record counts.

For instance, if a report consolidates transaction amounts of $4,000, $2,500, and $2,500 from two sources, Union All preserves all three rows and the total is $9,000. If the repeated $2,500 rows are genuine transactions, retaining both is necessary for a complete total. If they are duplicate extracts of the same transaction, the report needs a defined deduplication rule.

Best Practices for Accurate BAQ Results

Before building a Union All query, define the meaning of each output column and the level of detail each row represents. Align corresponding fields across query branches, and make sure null values, date fields, identifiers, and monetary amounts behave consistently. Clear field naming also helps users interpret the combined report correctly.

  • Keep corresponding columns in the same order and use compatible data types.
  • Confirm that each query branch applies the intended company, site, date, and transaction-status filters.
  • Compare the output row count with the sum of the source row counts before downstream filtering.
  • Reconcile financial totals against trusted Epicor screens or reports using the same criteria.
  • Document expected duplicate behavior and apply separate deduplication logic only when the business rule requires it.

Well-defined query outputs also support connected reporting and finance processes. Epicor Integration Finance concerns how ERP data connects with financial workflows, while Epicor ERP Modules help users identify the functional areas that supply relevant records. When extending epicor through integrations, consistent data definitions help downstream applications interpret consolidated results accurately.

Using Consolidated Data in Finance Workflows

Union All can prepare transaction datasets for reporting, but a consolidated query result is not itself a completed finance action. For example, a report may bring together customer payment records and remittance-related entries for review, while matching payments, resolving unapplied cash, and posting receipts require their own rules and workflow steps. How Hyperbots AI Agents 10x Epicor Finance Operations and Closing Epicor Finance Gaps with Hyperbots AI Agents explore how AI agents can extend Epicor finance processes around these activities.

Hyperbots Platform supports company-specific ERP integrations, workflows, roles, and general ledger structures through a no-code framework. Process Specific Capabilities provide process-focused automation, and Ready to Deploy Capabilities include pre-trained agents and ERP connectors for supported finance tasks. Human in the Loop workflows support review and approval, while Self Learning Capabilities use human feedback to refine supported decisions. These capabilities can complement consistent ERP data by connecting information with defined finance workflows.

Summary

Epicor BAQ Union All combines compatible query result sets into one output while preserving duplicate rows. It is useful for consolidating transaction data, comparing activity across sources, and preparing records for financial and operational reports. Align column structures, validate source filters, and reconcile row counts and totals so the combined dataset reflects the intended business rules.

Related Articles

Closing Epicor Finance Gaps with Hyperbots AI Agents How Hyperbots AI Agents 10x Epicor Finance Operations Are Duplicate GL Codes Wrecking Your Financial Reports? Master Data in SAP S/4HANA Hurts Finance Ops