I have two table that I am trying to join together. Proposed cost and Project. My join works as expected except for one entry where the Concept_ID_DFF (Project) used to join is null. Is there a way that I can join even though its blank.

Table (Project)

Table (Proposal)

Query Result

In short for query result I want to see another 3 row that is with date 2021-09-20 18:53:56.003, but this is coming in null. Is there a way to have it be 300000005876166. I expect to see 23241, 23241.0001, and 23241.0002. Is there a way that I can fix my code to compensate for the null value?

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


                                                                                                                    

CodePudding user response：

If I understand the ask you want it to default to 300000005876166 if null. You could put a function into the join to default to 300000005876166 on the project ID DFF columns.

See join below

FROM Financial_Repository.FACT_IM_TPE_PROPOSL_COST
    LEFT OUTER JOIN Financial_Repository.DIM_PRJ_HISTORY DIM_PRJ
     ON ISNULL(DIM_PRJ.CONCEPT_ID_DFF, '300000005876166') = FACT_IM_TPE_PROPOSL_COST.CONCEPT_MASTER_DSID

CodePudding user response：

Use a CROSS JOIN. For example:

select *
from project p
cross join proposal l

Result:

 SRC_START_DTTM           Concept_ID_DFF   SRC_START_DTTM  Concept_ID_DFF  
 ------------------------ ---------------- --------------- --------------- 
 2021-09-20 18:53:56.003  null             1262450.00      300000005876166 
 2021-09-20 18:54:22.150  300000005876166  1262450.00      300000005876166 
 2021-09-23 14:02:49.000  300000005876166  1262450.00      300000005876166 
 2021-09-20 18:53:56.003  null             1510546.00      300000005876166 
 2021-09-20 18:54:22.150  300000005876166  1510546.00      300000005876166 
 2021-09-23 14:02:49.000  300000005876166  1510546.00      300000005876166 
 2021-09-20 18:53:56.003  null             12874.00        300000005876166 
 2021-09-20 18:54:22.150  300000005876166  12874.00        300000005876166 
 2021-09-23 14:02:49.000  300000005876166  12874.00        300000005876166 

See running example at db<>fiddle.