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);
... View more