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

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

kuppusamy — Mon, 02 Jul 2018 13:18:13 GMT

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;

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

Kurt_Bremser — Mon, 02 Jul 2018 13:57:47 GMT

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

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

kuppusamy — Mon, 02 Jul 2018 14:14:47 GMT

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

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

SuryaKiran — Mon, 02 Jul 2018 14:29:10 GMT

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;

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

Kurt_Bremser — Mon, 02 Jul 2018 15:25:43 GMT

@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.