Exact Online全外连接后预算与实际行未合并问题排查

Let's break down why your full outer join isn't merging rows as expected, even when both actuals and budget exist for a dimension combination like 2019 Period 1, GL Account 5050.

Most Likely Root Causes #

1. Mismatched Data Types in Join Conditions #

Looking at your join clause, several pairs of fields are being compared without ensuring type consistency:

• bud.division = to_number(bl.division_code): You're converting bl.division_code to a number, but if bud.division is a string type, even identical-looking values (e.g., 123 vs "123") might not match implicitly in some databases.
• bud.reportingyear = bl.costcenter_costanalysis_period_periods_year_reportingyear_attr: If bud.reportingyear is numeric (like INT) and bl's field is a string (e.g., "2019"), implicit conversion can fail—especially if the string has hidden whitespace (e.g., "2019 ").
• The same type mismatch risk applies to reportingperiod and glaccountcode comparisons.

2. Hidden Character Differences #

Even if types match, subtle discrepancies like leading/trailing spaces, case sensitivity (unlikely for GL codes/years but possible), or non-printable characters can block matches. For example, bl's GL account might be " 5050" (with a leading space) while bud's is "5050".

3. Null Handling in Nested Fields #

The nested fields in balancelinesperperiodcostanalysis (like costcenter_costanalysis_period_periods_year_years_balance_code_attr) might return null for some rows. You're using coalesce to display the budget value after the join, which can make it look like dimensions match—even though the join itself failed because the underlying bl field was null.

Step-by-Step Fixes & Debugging #

First, Validate the Mismatch #

Run these queries to check raw values for the problematic dimension combination (2019 Period 1, GL 5050) in both tables:

-- Check actuals table values
SELECT
  to_number(division_code) AS bl_division,
  costcenter_costanalysis_period_periods_year_reportingyear_attr AS bl_reportingyear,
  costcenter_costanalysis_period_reportingperiod_attr AS bl_reportingperiod,
  costcenter_costanalysis_period_periods_year_years_balance_code_attr AS bl_glaccountcode,
  costcenter_code_attr AS bl_costcenter,
  code_attr AS bl_costunit
FROM exactonlinexml..balancelinesperperiodcostanalysis bl
WHERE
  costcenter_costanalysis_period_periods_year_reportingyear_attr = '2019'
  AND costcenter_costanalysis_period_reportingperiod_attr = '1'
  AND costcenter_costanalysis_period_periods_year_years_balance_code_attr = '5050';

-- Check budget table values
SELECT
  division AS bud_division,
  reportingyear AS bud_reportingyear,
  reportingperiod AS bud_reportingperiod,
  glaccountcode AS bud_glaccountcode,
  costcenter AS bud_costcenter,
  costunit AS bud_costunit
FROM exactonlinerest..budgets bud
WHERE
  reportingyear = 2019
  AND reportingperiod = 1
  AND glaccountcode = '5050';

Compare the outputs side-by-side—look for type differences, whitespace, or any other discrepancies.

Fix the Join Conditions #

Once you identify the mismatch, adjust the join to align types and clean up values:

full outer join exactonlinerest..budgets bud 
  on bud.division = to_number(bl.division_code)
  -- Align GL account type (convert to number if bud uses numeric GL codes)
  AND bud.glaccountcode = to_number(bl.costcenter_costanalysis_period_periods_year_years_balance_code_attr)
  -- Clean up whitespace for string fields
  AND TRIM(bud.costcenter) = TRIM(bl.costcenter_code_attr)
  AND TRIM(bud.costunit) = TRIM(bl.code_attr)
  -- Align reporting year/period types
  AND bud.reportingyear = to_number(bl.costcenter_costanalysis_period_periods_year_reportingyear_attr)
  AND bud.reportingperiod = to_number(bl.costcenter_costanalysis_period_reportingperiod_attr)

Adjust conversions based on actual data types (e.g., use TRIM instead of to_number if bud.glaccountcode is a string).

Verify the Source Field #

After fixing the join, the Source field should show both for combinations where both actuals and budget exist, instead of separate budget and balancelines rows.

内容的提问来源于stack exchange，提问作者H Jansen