Hi Everyone,
New hear and not a SQL super user. I have a code i run on a monthly basis and for some reason this month, it gives me the following error message: Unable to get a stable set of rows in the source table. I have not updated/changed the code at all and now i'm receiving this message. It says the error occurs at the last section "build sequence" (in bold below). I've read through some discussions on here and seems to be a duplicate/refresh error that system doesn't know how to move forward but i'm not sure how to re-write it. Can someone please assist?
Here is the SQL code:
--BUILD CUSTOMER 12 MONTH BASE
INSERT INTO TEMP_REPORT_OWNER.FACT_ACTIVE_REPEAT_BASE ( CUSTOMER_KEY, REPORT_MONTH, BUSINESS, COLUMN9)
SELECT ods_owner.fact_order_line.customer_key, TRUNC(TO_DATE(TO_DATE(&end_dt,'mm/dd/yyyy') ), 'MONTH'), 'D', count(distinct ODS_OWNER.FACT_ORDER_LINE.order_id)
FROM
ODS_OWNER.FACT_ORDER_LINE,
ODS_OWNER.DIM_STATUS_DEFINITION,
ODS_OWNER.DIM_DATE
WHERE
( ODS_OWNER.FACT_ORDER_LINE.ORDER_STATUS_KEY=ODS_OWNER.DIM_STATUS_DEFINITION.STATUS_KEY )
AND ( ODS_OWNER.DIM_DATE.DATE_KEY=ODS_OWNER.FACT_ORDER_LINE.ORDER_PLACED_DATE_KEY )
AND (case when ODS_OWNER.FACT_ORDER_LINE.web_pickup_order_promised_time is null then 'N' else 'Y' end) = 'N' --excludes WPU
AND (CASE WHEN (ODS_OWNER.DIM_STATUS_DEFINITION.status_cd in('PENDING_IN_STORE_PAYMENT','FAILED_IN_STORE_PAYMENT','FAILED','BAD_ORDER','NO_ACTION_REQUIRED','CORRUPT_ORDER')) then 'N' else 'Y' end ) = 'Y' --limits to true placed orders
AND DECODE(BITAND(ODS_OWNER.FACT_ORDER_LINE.ORDER_LINE_FLG, POWER(2 , 0)),POWER(2 , 0), '1', '0') = '0' --excludes courtesy orders
and ods_owner.fact_order_line.customer_key<>-1 --EXCLUDES GUEST ORDERS
AND nvl(ods_owner.fact_order_line.CANCEL_FLG,'N') <>'Y' -- excludes cancelled by customer, denied contact lens, and denied for fraud/abuse
--and ODS_OWNER.DIM_PRODUCT.FULFILLER_TYPE_DESCRIPTION not in('ContactLens', 'ContactLens/Store') --excludes contact lens
/*TIME PERIOD*/
AND ODS_OWNER.DIM_DATE.CURRENT_DATE>= ADD_MONTHS((TO_DATE(&end_dt,'mm/dd/yyyy')), -12)
AND ODS_OWNER.DIM_DATE.CURRENT_DATE <TO_DATE(&end_dt,'mm/dd/yyyy')
group by ods_owner.fact_order_line.customer_key, TRUNC(TO_DATE(TO_DATE(&end_dt,'mm/dd/yyyy') ), 'MONTH'), 'D'
;
MERGE
INTO TEMP_REPORT_OWNER.FACT_ACTIVE_REPEAT_BASE TGT
USING (
SELECT distinct t.CUSTOMER_KEY, t.REPORT_MONTH, T.BUSINESS, c.FRONT_END_FIRST_PURCH_DTTM as f_dt
FROM TEMP_REPORT_OWNER.FACT_ACTIVE_REPEAT_BASE T,
ods_owner.DIM_CUSTOMER c
WHERE T.CUSTOMER_KEY=c.CUSTOMER_KEY
AND T.BUSINESS='D'
and t.REPORT_MONTH=TO_DATE(&end_dt,'mm/dd/yyyy')
) SRC
ON ( SRC.CUSTOMER_KEY = tgt.CUSTOMER_KEY and src.REPORT_MONTH=tgt.REPORT_MONTH AND SRC.BUSINESS=TGT.BUSINESS)
WHEN MATCHED
THEN
UPDATE
SET tgt.FIRST_PURCHASE_DT= src.f_dt
;
--build sequence
MERGE
INTO TEMP_REPORT_OWNER.FACT_ACTIVE_REPEAT_BASE TGT
USING (
SELECT distinct O.CUSTOMER_KEY, o.REPORT_MONTH, o.BUSINESS,(CASE WHEN O.FIRST_PURCHASE_DT < ADD_MONTHS((TO_DATE(&end_dt,'mm/dd/yyyy')), -12) THEN O.COLUMN9 +1 ELSE O.COLUMN9 END)SQN
FROM TEMP_REPORT_OWNER.FACT_ACTIVE_REPEAT_BASE O
WHERE O.REPORT_MONTH=TO_DATE(&end_dt,'mm/dd/yyyy')
and o.BUSINESS='D'
)SRC
ON ( SRC.CUSTOMER_KEY = tgt.CUSTOMER_KEY and src.REPORT_MONTH=tgt.REPORT_MONTH and src.BUSINESS=tgt.BUSINESS)
WHEN MATCHED
THEN
UPDATE
SET tgt.SEQUENCE= src.SQN
;