06-09-2015 06:37 PM
Could anyone please tell me how to correct this code? the problem seems to be in the "compress" part of the sql
connect to oracle (user=xxx path='xxx' orapw="xxx");
create table Want as
from connection to oracle
from T1 a inner join Tq b
on a.rec=b.rec and a.place=b.place
b.place='Tx' and ( substr(compress(b.diagnosis,,'p'),1,3) = 'NYC' )
It seems sql/oracle dose not accept the compress theres, any ideas on how to solve this please?
06-09-2015 09:01 PM
You are using SQL passthru so your SQL must conform to the Oracle dialect. COMPRESS is a SAS function not valid in Oracle. There is a SUBSTR function in Oracle but it may not work exactly the same as SAS's.
I don't have Oracle to test on but I would check out the Oracle LIKE operator:
and b.diagnosis like 'NYC%'
06-10-2015 01:51 AM
Since everything in the "connection to oracle" is executed by the RDBMS, you best get help from your Oracle admins; they know best how to solve your problem in Oracle.