Hi Tom,
Great to visit ask tom site i'm keep on learning oracle things here. i have one issue pasted my view in the below. i have loaded with "SRC_key = 16" new source data into tables which we used in this view. here SRC_key is a column and 16 is a value. when i run my view i can't able to see src_key = 16 data. Kindly help me on this.
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);
Not really much we can do to help here because we can't see you tables and data etc...
But when faced with a SQL that is not returning data that I expect, I typically:
- break the SQL into smaller parts
- run each part to see where the data is being "dropped"
So you can remove join conditions one by one until you see where the logic error is.
Hope that helps.