Hi All, When i execute below query i am getting ORA-30926: unable to get a stable set of rows in the source tables error.
Query :
merge INTO PS_CT_IQN_ACC_STG1 STG USING PS_CTS_IQN_HOL_DAT H ON (H.HOLIDAY_SCHEDULE = STG.HOLIDAY_SCHEDULE
AND H.HOLIDAY = STG.REPORT_DUE_DATE
AND H.LOCATION = STG.LOCATION ) WHEN matched THEN
UPDATE SET STG.CTS_WORKDAY = 'H';
Below is My table and insert scripts :
CREATE TABLE PS_CTS_IQN_HOL_DAT (HOLIDAY_SCHEDULE VARCHAR2(6) NOT NULL,
HOLIDAY DATE,
LOCATION VARCHAR2(10) NOT NULL
)
/
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('05-JUN-2019','DD-MON-YYYY'),'AEAZABUC01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('05-JUN-2019','DD-MON-YYYY'),'AESHSHAC01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('06-JUN-2019','DD-MON-YYYY'),'AEAZABUA01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('06-JUN-2019','DD-MON-YYYY'),'AEAZABUC01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('06-JUN-2019','DD-MON-YYYY'),'AESHSHAC01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('12-AUG-2019','DD-MON-YYYY'),'AEAZABUA01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('12-AUG-2019','DD-MON-YYYY'),'AEAZABUC01');
Insert into PS_CTS_IQN_HOL_DAT1 (HOLIDAY_SCHEDULE,HOLIDAY,LOCATION) values ('HABD',to_date('12-AUG-2019','DD-MON-YYYY'),'AESHSHAC01');
/
CREATE TABLE PS_CT_IQN_ACC_STG1 (CTS_WORKDAY VARCHAR2(3) NOT NULL,
HOLIDAY_SCHEDULE VARCHAR2(6) NULL,
REPORT_DUE_DATE DATE,
LOCATION VARCHAR2(10) NOT NULL
)
/
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y',' ',to_date('31-AUG-2019','DD-MON-YYYY'),'USMOSITC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y',' ',to_date('31-AUG-2019','DD-MON-YYYY'),'USMTMISA01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y',' ',to_date('31-AUG-2019','DD-MON-YYYY'),'USPAJEUA01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y',' ',to_date('31-AUG-2019','DD-MON-YYYY'),'USTNNASC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y',' ',to_date('31-AUG-2019','DD-MON-YYYY'),'USVALEEC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONBRAC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONBURC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONLONC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONMISA01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONMISC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONTORA01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONTORA02');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONTORA03');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'CAONTORC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('08-JUN-2019','DD-MON-YYYY'),'USMIDEAC01');
Insert into PS_CT_IQN_ACC_STG1 (CTS_WORKDAY,HOLIDAY_SCHEDULE,REPORT_DUE_DATE,LOCATION) values ('Y','HCAN1',to_date('15-JUN-2019','DD-MON-YYYY'),'CAONBRAC01');
Message was edited by: Vemula Muni