Desktop productivity for business analysts and programmers

Code for extracting data from meta table

Reply
New Contributor
Posts: 3

Code for extracting data from meta table

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)-

snip1.PNG

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

Super User
Posts: 3,233

Re: Code for extracting data from meta table

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.

Ask a Question
Discussion stats
  • 1 reply
  • 345 views
  • 0 likes
  • 2 in conversation