DATA Step, Macro, Functions and more

Connect to Oracle(user= , password=) code is not working in sql inline view

Reply
Contributor
Posts: 46

Connect to Oracle(user= , password=) code is not working in sql inline view

 

I have written a mcro using connect to oracle statement as shown below, but its showing error:

ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis.

Is there anytthing wrong in my code, it seems inline view code is not supported in connect to oracle statement.

/**/

%MACRO

 

 

 

WHICH(PROCESS,COND,SRC_SYS);

PROC SQL outobs=

 

10000;

connect to oracle (user=u1411710 password=work1sbu path=

 

'misp');

CREATE TABLE CURRENT.AGG_RISK_COV_&PROCESS AS

select *

from connection to oracle

(SELECT

 

S.*,T.RISK_STATE_CD,T.COVRG_TYPE_CD FROM

(SELECT

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

YEAR_CT,

MONTH_CT,

BROKER_NM,

SUM(EARNED_XPSR_AM) AS EARNED_XPSR_AM,

SUM(WRITTEN_XPSR_AM) AS WRITTEN_XPSR_AM,

SUM(MTD_WRITTEN_PRM_AM)AS MTD_WRITTEN_PRM_AM,

SUM(EARNED_PRM_AM) AS EARNED_PRM_AM

FROM

(

SELECT

POLICY_TYPE_SK,

SOURCE_SYSTEM_SK, ACTUAL_DT,

 

 

/* YEAR(DATEPART(ACTUAL_DT)) AS YEAR_CT,*/

EXTRACT(YEAR FROM ACTUAL_DT) as YEAR_CT,

 

 

/* MONTH(DATEPART(ACTUAL_DT)) AS MONTH_CT,*/

EXTRACT(MONTH FROM ACTUAL_DT) as MONTH_CT,

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

BROKER_NM,

EARNED_XPSR_AM,

WRITTEN_XPSR_AM,

MTD_WRITTEN_PRM_AM,

EARNED_PRM_AM

FROM misp.TCOVERAGES_SUMMARY

LEFT JOIN misp.TDATE

ON TCOVERAGES_SUMMARY.MONTH_SK = TDATE.DATE_SK

LEFT JOIN misp.TBROKER_DIMENSION

ON TBROKER_DIMENSION.BROKER_SK = TBROKER_DIMENSION.BROKER_SK

WHERE POLICY_TYPE_SK IN (

 

1,15) AND

SOURCE_SYSTEM_SK &COND IN (&

 

SRC_SYS.) AND

SNAPSHOT_TYPE_IN =

 

0 AND

EXTRACT(YEAR FROM ACTUAL_DT) >= &

 

START_YEAR. AND

EXTRACT(YEAR FROM ACTUAL_DT) <= &

 

END_YEAR.) AS R

 

GROUP BY

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

BROKER_NM,

YEAR_CT,

MONTH_CT ) AS S

LEFT JOIN

misp.TPOLICY

ON S.POLICY_SK = TPOLICY.POLICY_SK

LEFT JOIN

misp.TCOVRG_TYPE_DMNSN

ON S.INTCOV_SK = TCOVRG_TYPE_DMNSN.COVERAGE_TYPE_SK )AS T;

disconnect from oracle;

QUIT;

%MEND

 

 

 

;

OPTIONS

 

 

SYMBOLGEN MLOGIC MPRINT;

%LET

 

 

SRC_SYS_ALL = %STR(2,3,4,14,15);

%WHICH(PROCESS= PCG_&YYQ,COND=NOT,SRC_SYS=&SRC_SYS_ALL);

 

Super User
Posts: 17,810

Re: Connect to Oracle(user= , password=) code is not working in sql inline view

I think the error is correct, you have unbalanced parentheses. 

 

Count number of open/close parentheses. This is how I find these errors. 

Super User
Posts: 9,676

Re: Connect to Oracle(user= , password=) code is not working in sql inline view

There must be some error in your SQL.

use the following option to check what kind of SQL SAS has passed into ORACLE .

 

options sastrace=',,,d' sastraceloc=log;

Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 3 in conversation