表连接时Concept_ID_DFF为NULL，如何实现关联并得到预期结果？

问题说明 #

关联Proposal（Financial_Repository.FACT_IM_TPE_PROPOSL_COST）与Project（Financial_Repository.DIM_PRJ_HISTORY）表时，因Project表中Concept_ID_DFF为NULL的行无法通过原关联条件匹配，导致缺失对应日期2021-09-20 18:53:56.003的3条记录。需将该NULL值视为300000005876166，使查询结果包含23241、23241.0001、23241.0002三类~Project值。

数据表示例 #

Project表（DIM_PRJ_HISTORY）数据 #

Proposal表（FACT_IM_TPE_PROPOSL_COST）数据 #

当前SQL语句 #

SELECT  CONVERT(bigint, FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID) AS [~Proposal]
    ,   FACT_IM_TPE_PROPOSL_COST.VERSION AS [Cost Version]
    ,   DATEFROMPARTS(TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR), 1, 1) AS [~Reporting Period]
    ,   DIM_PRJ.SRC_START_DTTM
    ,   DIM_PRJ.PRJ_STAT_CD
    ,   DIM_PRJ.CONCEPT_ID_DFF
    ,   DIM_PRJ.DWID
    ,   CONVERT(float, (DIM_PRJ.DWID + (ROW_NUMBER() OVER (PARTITION BY DIM_PRJ.DWID, FACT_IM_TPE_PROPOSL_COST.CST_AMT  ORDER BY DIM_PRJ.SRC_END_DTTM, TRY_CONVERT(bigint, DIM_PRJ.PRJ_STAT_CD))*.0001)) - .0001) AS [~Project]
    ,   TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR) AS [Cost Year]
    ,   FACT_IM_TPE_PROPOSL_COST.CST_AMT AS [Proposed Cost] 
FROM Financial_Repository.FACT_IM_TPE_PROPOSL_COST
    LEFT OUTER JOIN Financial_Repository.DIM_PRJ_HISTORY DIM_PRJ
        ON DIM_PRJ.CONCEPT_ID_DFF = FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID
WHERE DIM_PRJ.DWID = '23241' AND [Version] = '1'
ORDER BY [~Project] ASC

修改后的SQL语句 #

SELECT  CONVERT(bigint, FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID) AS [~Proposal]
    ,   FACT_IM_TPE_PROPOSL_COST.VERSION AS [Cost Version]
    ,   DATEFROMPARTS(TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR), 1, 1) AS [~Reporting Period]
    ,   DIM_PRJ.SRC_START_DTTM
    ,   DIM_PRJ.PRJ_STAT_CD
    ,   DIM_PRJ.CONCEPT_ID_DFF
    ,   DIM_PRJ.DWID
    ,   CONVERT(float, (DIM_PRJ.DWID + (ROW_NUMBER() OVER (PARTITION BY DIM_PRJ.DWID, FACT_IM_TPE_PROPOSL_COST.CST_AMT  ORDER BY DIM_PRJ.SRC_END_DTTM, TRY_CONVERT(bigint, DIM_PRJ.PRJ_STAT_CD))*.0001)) - .0001) AS [~Project]
    ,   TRY_CONVERT(int, FACT_IM_TPE_PROPOSL_COST.CST_YEAR) AS [Cost Year]
    ,   FACT_IM_TPE_PROPOSL_COST.CST_AMT AS [Proposed Cost] 
FROM Financial_Repository.FACT_IM_TPE_PROPOSL_COST
    LEFT OUTER JOIN Financial_Repository.DIM_PRJ_HISTORY DIM_PRJ
        -- 将NULL值替换为指定的Concept_ID_DFF值，实现匹配
        ON ISNULL(DIM_PRJ.CONCEPT_ID_DFF, 300000005876166) = FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID
WHERE DIM_PRJ.DWID = '23241' AND [Version] = '1'
ORDER BY [~Project] ASC

修改说明 #

• 在JOIN关联条件中，使用ISNULL()函数将Project表（DIM_PRJ_HISTORY）中Concept_ID_DFF的NULL值替换为目标值300000005876166，确保其能匹配到Proposal表的对应记录。
• 保留原有的LEFT OUTER JOIN逻辑，保证所有符合条件的Proposal记录都能被查询到，同时包含Project表中NULL值对应的行。

内容的提问来源于stack exchange，提问作者Vince Mack