应用 where 条件时无法从表中获取数据

Question

Unable to get the data from the table after applying where conditio0n. manually checked all the tables the data is present in it.

Need help in resolving this issue.

I tried changing the where condition but still unable to get the data.

I assume the issue is in below condition however I'm unable to identify it

Below is the code:

SELECT
    a.OpportunityCode AS KPMG_Opportunity__c,
    CompetitorCode AS Account__c,
    CASE
        WHEN
            b.StageDescription = 'Won' 
        THEN
            'True' 
        ELSE
            'False' 
    END
    AS KPMG_Won_Opportunity__c, c.ConvertedOutcomeReasonCode, b.CloseDate, b.etl_rec_eff_end_ts, d.IsArchived 
FROM
    bpg_crm_opportunity_refined.oppty_competitors AS a 
    LEFT JOIN
        bpg_crm_opportunity_refined.oppty_header AS b 
        ON a.opportunitycode = b.OpportunityCode 
    LEFT JOIN
        bpg_oppty_mgmt_trusted.outcomereason_conversion_lkp c 
        ON b.OpportunityStatusCode_Orig = c.StatusCode 
    LEFT JOIN
        bpg_crm_engagement_trusted.edw_dim_entity d 
        ON b.PrimaryAccountCode = d.EntityID 
WHERE
    d.IsArchived != 'false' 
    and c.ConvertedOutcomeReasonCode != 'Z004A' 
    and date_part('Year', b.etl_rec_eff_end_ts) = '9999'

应用where条件0n后无法从表中获取数据。手动检查其中存在数据的所有表。

需要帮助来解决这个问题。

我尝试更改where条件但仍然无法获取数据。

我认为问题出在以下情况，但我无法识别它

Answer 1

SELECT
a.OpportunityCode AS KPMG_Opportunity__c,
CompetitorCode AS Account__c,
CASE
    WHEN
        b.StageDescription = 'Won' 
    THEN
        'True' 
    ELSE
        'False' 
END
AS KPMG_Won_Opportunity__c, 
 c.ConvertedOutcomeReasonCode, b.CloseDate, b.etl_rec_eff_end_ts, d.IsArchived 
FROM
bpg_crm_opportunity_refined.oppty_competitors AS a 
LEFT JOIN
(
  SELECT OpportunityCode,OpportunityStatusCode_Orig,PrimaryAccountCode,
         StageDescription,CloseDate,etl_rec_eff_end_ts
   FROM  bpg_crm_opportunity_refined.oppty_header
   WHERE b.etl_rec_eff_end_ts BETWEEN '99990101'AND '99991231'
)AS b ON a.opportunitycode = b.OpportunityCode 
LEFT JOIN
(
  SELECT ConvertedOutcomeReasonCode,StatusCode
  FROM 
    bpg_oppty_mgmt_trusted.outcomereason_conversion_lkp c 
    WHERE ConvertedOutcomeReasonCode != 'Z004A' 
)AS c    ON b.OpportunityStatusCode_Orig = c.StatusCode 
LEFT JOIN
(
  SELECT EntityID,IsArchived
  FROM 
    bpg_crm_engagement_trusted.edw_dim_entity  
   WHERE IsArchived != 'false' 
) D   ON b.PrimaryAccountCode = d.EntityID 

可能是这样的