Oracle 无法在视图中看到SRC_KEY = 16数据。

嗨，汤姆，
很高兴访问问汤姆网站，我在这里继续学习甲骨文的东西。我有一个问题粘贴了我的视图在下面。我已经将 “SRC_key = 16” 新源数据加载到我们在此视图中使用的表中。这里SRC_key是列，16是值。当我运行视图时，我无法看到src_key = 16数据。请帮我。

  CREATE OR REPLACE FORCE VIEW "LH_REPEXT"."VW_LH_TIME_TO_FILL" ("MDM_PAT_ID", "BRAND_NM", "SRC_KEY", "SOURCE_NAME", "SPP_FIRST_RFRL_STAT_DT", "SPP_FIRST_SHIPMENT_DT", "RX_FIRST_WRITTEN_DT", "SPP_TIME_TO_FILL", "OVERALL_FIRST_RFRL_STAT_DT", "OVERALL_FISRT_SHIPMENT_DT", "OVERALL_TIME_TO_FILL", "OVERALL_FIRST_RFRL_SOURCE", "OVERALL_FIRST_SHP_SOURCE", "PAYER_NAME", "PLAN_TYPE", "TERRITORY_NAME", "RFRL_SOURCE", "REFFERAL_MONTH_END_DT", "FLAG") AS 
  SELECT "MDM_PAT_ID", prod_name AS "BRAND_NM", "SRC_KEY", "SOURCE_NAME",
          "SPP_FIRST_RFRL_STAT_DT", "SPP_FIRST_SHIPMENT_DT",
          "RX_FIRST_WRITTEN_DT", "SPP_TIME_TO_FILL",
          "OVERALL_FIRST_RFRL_STAT_DT", "OVERALL_FISRT_SHIPMENT_DT",
          "OVERALL_TIME_TO_FILL", "OVERALL_FIRST_RFRL_SOURCE",
          "OVERALL_FIRST_SHP_SOURCE", "PAYER_NAME", "PLAN_TYPE",
          "TERRITORY_NAME", "RFRL_SOURCE", "REFFERAL_MONTH_END_DT",
          CASE
             WHEN spp_time_to_fill IS NOT NULL
             AND NVL (overall_time_to_fill, 0) > 0
                THEN 'Y'
             ELSE 'N'
          END flag
     FROM (WITH rfrl AS
                (SELECT mdm_pat_id, prod_name, product_id, src_key, pat_key,
                        crd, prmry_pyr_key, rx_written_dt
                   FROM (SELECT a.mdm_pat_id, b.prod_name, b.product_id,
                                a.src_key, a.pat_key pat_key,
                                b.curr_rfrl_stat_dt crd, prmry_pyr_key,
                                b.rx_written_dt,
                                ROW_NUMBER () OVER (PARTITION BY mdm_pat_id, b.product_id, b.src_key ORDER BY b.curr_rfrl_stat_dt,
                                 b.spp_referral_key) AS row_num
                           FROM lh_ods.ods_raw_patient a,
                                (SELECT spp_rfrl.*, rpd.prod_name
                                   FROM lh_ods.ods_spp_referral spp_rfrl,
                                        lh_ods.lh_report_prod_dtls rpd
                                  WHERE spp_rfrl.product_id = rpd.prod_id
                                    AND rpd.report_flag = 'Y') b
                          WHERE a.pat_key = b.pat_key
                            AND a.insert_src_filename NOT LIKE '%LH%FIX%'
                                                                         --AND a.mdm_pat_id <> 3374
                        )
                  WHERE row_num = 1
                 UNION
                 SELECT mdm_pat_id, prod_name, product_id, src_key, pat_key,
                        crd, prmry_pyr_key, rx_written_dt
                   FROM (SELECT a.mdm_pat_id, d.prod_name, d.product_id,
                                a.src_key, a.pat_key pat_key,
                                d.curr_rfrl_stat_dt crd, prmry_pyr_key,
                                d.rx_written_dt,
                                ROW_NUMBER () OVER (PARTITION BY mdm_pat_id, d.product_id, d.src_key ORDER BY d.curr_rfrl_stat_dt,
                                 d.hub_key) AS row_num
                           FROM lh_ods.ods_raw_patient a,
                                (SELECT hub_data.*, rpd.prod_name
                                   FROM lh_ods.ods_hub_data hub_data,
                                        lh_ods.lh_report_prod_dtls rpd
                                  WHERE hub_data.product_id = rpd.prod_id
                                    AND rpd.report_flag = 'Y') d
                          WHERE a.pat_key = d.pat_key
                            AND a.insert_src_filename NOT LIKE '%LH%FIX%'
                                                                         --AND a.mdm_pat_id <> 3374
                        )
                  WHERE row_num = 1),
                shp AS
                (SELECT *
                   FROM (SELECT rfrl.mdm_pat_id mdm_pat_id, c.product_id,
                                rfrl.src_key src_key, c.rfrl_source,
                                c.shipment_dt sd, c.rx_written_dt,
                                ROW_NUMBER () OVER (PARTITION BY mdm_pat_id, c.product_id, rfrl.src_key ORDER BY c.shipment_dt,
                                 c.spp_shipment_key) AS row_num
                           FROM rfrl,
                                (SELECT spp_shp.*
                                   FROM lh_ods.ods_spp_shipment spp_shp,
                                        lh_ods.lh_report_prod_dtls rpd
                                  WHERE spp_shp.product_id = rpd.prod_id
                                    AND rpd.report_flag = 'Y') c
                          WHERE rfrl.pat_key = c.pat_key(+)
                                AND rfrl.src_key = c.src_key(+))
                  WHERE row_num = 1),
                overall AS
                (SELECT   rfrl.mdm_pat_id, rfrl.product_id,
                          MIN (rfrl.crd) ocrd, MIN (shp.sd) osd
                     FROM rfrl, shp
                    WHERE rfrl.mdm_pat_id = shp.mdm_pat_id
                      AND rfrl.product_id = shp.product_id
                 GROUP BY rfrl.mdm_pat_id, rfrl.product_id),
                src AS
                (SELECT rfrl.mdm_pat_id, rfrl.product_id,
                        CASE
                           WHEN overall.mdm_pat_id = rfrl.mdm_pat_id
                           AND overall.ocrd = rfrl.crd
                              THEN rfrl.src_key
                        END rskey,
                        CASE
                           WHEN overall.mdm_pat_id = rfrl.mdm_pat_id
                           AND overall.osd = NVL (shp.sd, '')
                              THEN shp.src_key
                        END sskey
                   FROM overall, rfrl, shp
                  WHERE (    overall.mdm_pat_id = shp.mdm_pat_id
                         AND overall.product_id = shp.product_id
                        )
                    AND (    overall.mdm_pat_id = rfrl.mdm_pat_id
                         AND overall.product_id = rfrl.product_id
                        ))
           SELECT   rfrl.mdm_pat_id, rfrl.prod_name, rfrl.src_key,
                    ms.src_nm source_name, rfrl.crd spp_first_rfrl_stat_dt,
                    shp.sd spp_first_shipment_dt,
                    CASE
                       WHEN NVL (rfrl.rx_written_dt, '') IS NULL
                          THEN shp.rx_written_dt
                       WHEN TRUNC (rfrl.rx_written_dt) >
                                TRUNC (shp.rx_written_dt)
                          THEN shp.rx_written_dt
                       ELSE rfrl.rx_written_dt
                    END rx_first_written_dt,
                    (TRUNC (shp.sd) - TRUNC (rfrl.crd)) spp_time_to_fill,
                    TRUNC (overall.ocrd) overall_first_rfrl_stat_dt,
                    TRUNC (overall.osd) overall_fisrt_shipment_dt,
                    (TRUNC (overall.osd) - TRUNC (overall.ocrd)
                    ) overall_time_to_fill,
                    ms2.src_nm overall_first_rfrl_source,
                    ms3.src_nm overall_first_shp_source,
                    NVL (pyr2.pyr_nm, pyr.pyr_nm) payer_name,
                    pyr.pln_typ plan_type, pyr.territory_nm territory_name,
                    shp.rfrl_source,
                    LAST_DAY (TRUNC (rfrl.crd)) refferal_month_end_dt
               FROM rfrl,
                    shp,
                    overall,
                    lh_ods.ods_master_source ms,
                    src,
                    lh_ods.ods_master_source ms2,
                    lh_ods.ods_master_source ms3,
                    lh_ods.ods_raw_payer pyr,
                    lh_ods.ods_master_payer pyr2
              WHERE rfrl.mdm_pat_id = shp.mdm_pat_id(+)
                AND rfrl.product_id = shp.product_id(+)
                AND rfrl.src_key = shp.src_key
                AND rfrl.src_key = ms.src_key
                AND src.rskey = ms2.src_key
                AND src.sskey = ms3.src_key
                AND rfrl.prmry_pyr_key = pyr.pyr_key(+)
                AND rfrl.src_key = pyr.src_key(+)
                AND pyr.master_pyr_pln_id = pyr2.master_pyr_pln_id(+)
                AND rfrl.mdm_pat_id = src.mdm_pat_id(+)
                AND rfrl.mdm_pat_id = overall.mdm_pat_id(+)
           ORDER BY 1, 2);

我们在这里无能为力，因为我们看不到您的表和数据等。

但是，当面对不返回我期望的数据的SQL时，我通常:

-将SQL分解为较小的部分
-运行每个部分以查看数据被 “丢弃” 的位置

因此，您可以逐个删除连接条件，直到您看到逻辑错误在哪里。

希望能有所帮助。