Code for extracting data from meta table

[mray]

Hello,

I am a beginner with connecting to metatables and accessing the data from there.  Usually I used to have the tables locally on my desktop.

I am confused about what is wrong in this code.  Showing the code and the error generated as well as the desired output.

Desired output (just a snippet)-

SAS Code-

proc sql;

  connect to oracle(authdomain="OracleAuth" buffsize=32767 preserve_comments path = 'roscop');

  execute (alter session set nls_date_format = 'mm/dd/yyyy') by oracle ;

   create table mray2 as

    select *

    from connection to oracle

(SELECT
  CNTCT.CNTCT_OPEN_DT,
  CNTCT.CNTCT_TYPE_DESC,
  CNTCT.CNTCT_INIATR_DESC,
  sum(decode(CNTCT.CNTCT_NEW_IND,'Y',1,0)),
  CNTCT.SRC_SYS_DESC
FROM
  CNTCT
WHERE
  CNTCT.CNTCT_OPEN_DT  BETWEEN  '01-01-2015 00:00:00'dt  AND  '21-08-2015 00:00:00'dt
GROUP BY
  CNTCT.CNTCT_OPEN_DT,
  CNTCT.CNTCT_TYPE_DESC,
  CNTCT.CNTCT_INIATR_DESC,
  CNTCT.SRC_SYS_DESC

  )

    ;

    disconnect from oracle;

QUIT;

Error MSG-

ERROR: ORACLE prepare error: ORA-00905: missing keyword. SQL statement: SELECT CNTCT.CNTCT_OPEN_DT, CNTCT.CNTCT_TYPE_DESC,

       CNTCT.CNTCT_INIATR_DESC, sum(decode(CNTCT.CNTCT_NEW_IND,'Y',1,0)), CNTCT.SRC_SYS_DESC FROM CNTCT WHERE CNTCT.CNTCT_OPEN_DT

       BETWEEN '01-01-2015 00:00:00'dt AND '21-08-2015 00:00:00'dt GROUP BY CNTCT.CNTCT_OPEN_DT, CNTCT.CNTCT_TYPE_DESC,

Any help appreciated!

Thank you.

Monika

[SASKiwi]

You are using SQL passthru so your SQL must conform to Oracle's requirements.

'01-01-2015 00:00:00'dt is not a valid Oracle datetime string. Try removing the DT on the end - this is SAS-specific syntax. Just using a date like '01-JAN-2015' may also work.