Help with compress in Sql please

[ammarhm]

Hi

Could anyone please tell me how to correct this code? the problem seems to be in the "compress" part of the sql

proc sql;

connect to oracle (user=xxx path='xxx' orapw="xxx");

create table Want as

select

  id,

  place,

  date

  from connection to oracle

           (

           select

                      a.id,

                      a.place,

                      a.date

           from T1 a inner join Tq b

                      on a.rec=b.rec and a.place=b.place

           where

                     (

   b.place='Tx'  and ( substr(compress(b.diagnosis,,'p'),1,3) = 'NYC'  )

                      )

           );

quit;

It seems sql/oracle dose not accept the compress theres, any ideas on how to solve this please?

Best regards

[SASKiwi]

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%'

[Kurt_Bremser]

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.