ERROR:This task requires reading obs in random order, but engine allows only sequential access

[KermitTheFrog]

I am trying to update an oracle table.

I encounter this error when running the following data step:

data oracle.have(drop=_:);
    modify oracle.have end=last;
    if _n_=1 then do;
        declare hash h1(dataset:'update');
        declare hiter hh1('h1');
        _rc = h1.defineKey('id','tid','valid_to');
        _rc = h1.defineData('valid_from');
        _rc = h1.defineDone();
    end;

    if h1.find()=0 then do;
        replace;
        _rc = h1.remove();
    end;
run;

ERROR: File ORACLE.HAVE.DATA is sequential. This task requires reading observations in a random order, but the engine allows only sequential access.

I assume this is because of the modify statement... Is there any way to bypass this error ? The only actual way I found to this day is to use proc sql with an update statement.

[SASKiwi]

AFAIK, hash solutions will only work with SAS datasets. The MODIFY statement can be used with caution against RDBMSs as demonstrated in this interesting paper: https://www.lexjansen.com/pnwsug/2008/CurtisMack-Modify.pdf

[KermitTheFrog]

Thanks for your prompt answer @SASKiwi.

Can you tell me what could be the most efficient way to do this kind of update not using hash then ?

[SASKiwi]

@KermitTheFrog - It would help if you explained what your update is trying to do.

[KermitTheFrog]

@SASKiwi Sure.

Here is a minimal reproducible example:

data have;
id="ID1"; tid=1; valid_from="20NOV2017"d; valid_to="21MAR2020"d; output;
id="ID2"; tid=2; valid_from="04FEB2016"d; valid_to="31MAR2020"d; output;
id="ID3"; tid=3; valid_from="20NOV2017"d; valid_to="21MAR2020"d; output;
id="ID4"; tid=4; valid_from="20NOV2017"d; valid_to="21MAR2020"d; output;
format valid_from valid_to date9.;
run;

data _update;
id="ID1"; tid=1; valid_from="01OCT2017"d; valid_to="21MAR2020"d; dim="FROM";  output;
id="ID3"; tid=3; valid_from="01OCT2017"d; valid_to="21MAR2020"d; dim="FROM"; output;
id="ID4"; tid=4; valid_from="20NOV2017"d; valid_to="30JUN2021"d; dim="TO"; output;
format valid_from valid_to date9.;
run;

What I am trying to do is to update the valid_from (or valid_to) from the have table based on the value of the valid_from (or valid_to) on the _update table.
I got a variable (dim) that helps me flag which validity range to update.
For the time being, the following seems to work:

proc sql;
update have t1
	   set valid_from = (select valid_from from _update t2
	   					 where t1.id = t2.id
						 	and t1.tid = t2.tid
							and t1.valid_to = t2.valid_to
							and t2.dim="FROM")
	  where catx('#',id, tid, valid_to) in (select catx('#',id, tid, valid_to)
			                        from _update t3
				                where t3.dim="FROM");
quit;
proc sql;
update have t1
	  set valid_to = (select valid_to from _update t2
		   			  where t1.id = t2.id
						and t1.tid = t2.tid
					    and t1.valid_from = t2.valid_from
						and t2.dim="TO")
		  where catx('#', id, tid, valid_from) in (select catx('#', id, tid, valid_from)  
					                   from _update t3
							   where t3.dim="TO");
quit;

However, using the catx() function in the where statement seems a bit shaky. I am also sure there is a better way to do this in "one way", without the dim variable to be used.