DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 21

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

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;

Super User
Posts: 10,215

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

Posted in reply to kuppusamy

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

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

Posted in reply to KurtBremser

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

Super User
Posts: 10,215

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

Posted in reply to kuppusamy

@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 Smiley Wink . I suggest you get hold of one of your local Oracle DBA's to check your code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 590

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

Posted in reply to kuppusamy

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;

 

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 4 replies
  • 85 views
  • 0 likes
  • 3 in conversation