Error Proc Sql connect to

[jhh197]

PROC SQL;

CONNECT to ORACLE as EX(authdomain=yyyy path=xxx connection=global);

CREATE TABLE TEST20

AS

SELECT * FROM CONNECTION TO EX (

WITH TMP_HD AS

(

SELECT

CDR.* ,

MF.DATE,

MF.CD

FROM

SP_V.TEST1 CDR

LEFT JOIN SP_V.TEST2 MF

ON MF.ID=CDR.ID

WHERE

CDR.ID =100

),

TMP_SPL AS

(

SELECT

SPL.REGION,

PCS.TYPE

FROM SP_V.TEST3 SPL

JOIN TMP_HD S

ON SPL.REGION = S.REGION

JOIN SP_V.TEST4 PCS

ON SPL.REGION = PCS.REGION

)

SELECT H.*, F.*

FROM TMP_HD H

LEFT JOIN TMP_SPL F

ON H.REGION = F.REGION

);

DISCONNECT FROM EX ;

QUIT;

When I run above code it gives below error

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

Can anyone please help .

[ChrisNZ]

Please reformat your code.

As it is, maybe a rogue character has entered it, probably in one of the WITH clauses, and maybe near the end. Remove all spaces and retype them.

A proper editor such as Notepad++ can display all the characters.

[SASKiwi]

The error is perfectly obvious. Count the number of left parentheses (4) and the number of right parentheses (3) in the posted code. You are missing one right parentheses to balance your syntax. As to where it should go I'll leave that up to you as you are using Oracle-specific syntax and type of joining you are doing is not clear to me.

[ChrisNZ]

I must be blind, I see balanced parentheses.