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