ORA-30926: unable to get a stable set of rows in the source tables.

[kuppusamy]

the below bolded text is the logic. inthat im getting this error.

proc sql feedback;
connect to &db_engine as &db_engine (authdomain=&ecm_dbauth &ecm_db_conopts.);
execute (
merge into party_live pl
using
(select party_rk, version_no, party_full_nm, valid_from_dttm from iem_party_staging) tmp_iem
on (tmp_iem.party_rk = pl.party_rk)
when matched then update set
pl.version_no = tmp_iem.version_no,
pl.valid_from_dttm = tmp_iem.valid_from_dttm,
pl.party_full_nm = tmp_iem.party_full_nm
%if %upcase(&db_engine) eq ODBC %then %do;
;
%end;
) by &db_engine;
disconnect from &db_engine;
quit;

SYMBOLGEN: Macro variable DB_ENGINE resolves to oracle
MPRINT(FCF_IEM_UPDATE_SUBJECT):

execute ( merge into party_live pl using (select party_rk, version_no, party_full_nm,
valid_from_dttm from iem_party_staging) tmp_iem on (tmp_iem.party_rk = pl.party_rk) when matched then update set pl.version_no =
tmp_iem.version_no, pl.valid_from_dttm = tmp_iem.valid_from_dttm, pl.party_full_nm = tmp_iem.party_full_nm ) by oracle;
ERROR: ORACLE execute error: ORA-30926: unable to get a stable set of rows in the source tables.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
SYMBOLGEN: Macro variable DB_ENGINE resolves to oracle
MPRINT(FCF_IEM_UPDATE_SUBJECT): disconnect from oracle;

NOTE: Statement not executed due to NOEXEC option.
MPRINT(FCF_IEM_UPDATE_SUBJECT): quit;

[Kurt_Bremser]

According to https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-sour..., this is caused by duplicates returned by the "using" clause. Try a distinct in there.

[kuppusamy]

i modified to distinct party_rk. it is not working.......can you edit above code..

[Kurt_Bremser]

---

@kuppusamy wrote:

i modified to distinct party_rk. it is not working.......can you edit above code..

---

I can't test anything, as I do not have Oracle, I only have SAS and google 😉 . I suggest you get hold of one of your local Oracle DBA's to check your code.

[SuryaKiran]

Hi,

Is iem_party_staging temporary table? How did you create that temp table? Temp tables in DBMS exists only for that particular session. You need to setup connection in such a way that multiple DATA steps and PROC steps share the same connection. This can be done by using CONNECTION=GLOBAL. 

If your creating the temp table in one pass-through statement and accessing in other pass-through statement will not wok if you don't mention connection=Global. 

/* Run this and see if your temp table actually exists */

 proc sql;
connect to &db_engine as &db_engine (authdomain=&ecm_dbauth &ecm_db_conopts.);
CREATE TABLE temp AS 
SELECT * 
FROM CONNECTION TO &db_engine 

 (Select  * from iem_party_staging);

DISCONNECT FROM &db_engine;
QUIT;