Error with passthrough to oracle

[ammarhm]

Hi everyone

I am using the following code to connect ot oracle:

proc sql;
connect using datax as oracle;
create table combined_user as
select 
	*
	
from connection to oracle
	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on a.id=b.id and a.location=b.location
	);
quit;

This is working fine without issues.

Due to problems with id coding, I needed to remove some punctuations in the column id from the first table. i tried the following 

proc sql;
connect using datax as oracle;
create table combined_user as
select 
	*
	
from connection to oracle
	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on compress(a.id,,'p')=b.id and a.location=b.location
	);
quit;

which generated an error, possibly because oracle does not understand the command compress. 

Any suggestions on how to solve this? I tried reg_replace but with no luck

Another question, is there anything in oracle that can do something similar to matching by "like"?

	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on compress(a.id,,'p') like b.id and a.location=b.location
	);
quit;

Kind regards

Am

[r_behata]

Your question has the answer in it. You cannot use the sas functions inside the SQL Pass-thru query.

You should perhaps use the Oracle equivalent for compress which is TRIM , which removes the leading and trailing blanks and you can also add the punctuation to the list of characters to the TRIM function which you are trying to remove in the query.

[ChrisNZ · Accepted solution]

You can't use SAS function in an Oracle query, just like you can't use Oracle functions in a SAS query.

replace(translate(ID, '.,-:+*/;', '#'), '#', '')

does what you want.

[ChrisNZ]

regexp_replace(ID, '[-.,:+*/;]') 

works too.

[ChrisNZ]

proc sql;
connect using datax as oracle;
create table combined_user as
select *
from connection to oracle
(
select distinct *
from datax.dbo.process a left join datax.dbo.users b
on compress(a.id,,'p')=b.id and a.location=b.location
)
order by 1;
quit;

Just a reminder:

The code in blue runs in SAS and requires SAS syntax.

The code in red runs in Oracle and requires Oracle syntax.

[ammarhm]

Thanks all,

Any suggestions about using LIKE with wilde card % in Oracle?