ORA-00907: missing right Parenthesis error

building semantic layer
1

Hi, I’m trying to fix my universe in Universe design tool but I seem to keep getting errors. the Exception error in my derived tables. I have already made all my table joins with no issues. Here is a couple examples of my derived table SQL expressions:

  1. SELECT C.CON_CD,
    S.ROW_ID,
    S.LAST_UPD,
    S.X_SFDC_ID,
    SF.ID,
    SF.SIEBELUSERROWID_C,
    SF.LASTMODIFIEDDATE,
    SF.SIEBELQUEUEDATE_C,
    SF.SIEBELQUEUEDATE_C_TSTAMP,
    S.X_SFDC_LAST_UPD,
    TO_CHAR (SF.SIEBELQUEUEDATE_C, ‘yyyy’) AS YEAR_OF_SIEBEL_QUEUE_DATE,
    (CAST (S.X_SFDC_LAST_UPD AS TIMESTAMP)) AS X_SFDC_LAST_UPD_TS
    FROM BESTDATA.S_USER S
    JOIN BESTDATA.S_CONTACT C ON S.PAR_ROW_ID = C.ROW_ID
    LEFT OUTER JOIN SFDATA.BO_SF_S_USER SF ON S.X_SFDC_ID = SF.ID
    WHERE S.ROW_ID IS NOT NULL
    AND SF.ID IS NULL
    AND C.BU_ID = ‘0-R9NH’
    AND SF.SIEBELUSERROWID_C IS NULL
    AND C.CON_CD IN (‘Producer’, ‘Firm Contact’, ‘Employee’);

  2. SELECT S.ROW_ID,
    S.CON_CD,
    S.X_SFDC_CONTACT_ID,
    (CAST (S.X_SFDC_LAST_UPD AS TIMESTAMP)) AS X_SFDC_LAST_UPD_TIMESTAMP,
    SF.ID,
    SF.SIEBELQUEUEDATE_C,
    –SF.SIEBELQUEUEDATE_C_TSTAMP,
    SF.SIEBELCONTACTROWID_C,
    S.LAST_UPD,
    TO_CHAR (SF.SIEBELQUEUEDATE_C, ‘yyyy’) AS YEAR_OF_SIEBEL_QUEUE_DATE,
    SF.CONTACTTYPE_C,
    SF.CONTACTCONVERSIONTYPE_C
    FROM BESTDATA.S_CONTACT S
    LEFT OUTER JOIN SFDATA.BO_SF_S_CONTACT SF ON S.X_SFDC_CONTACT_ID = SF.ID
    WHERE S.X_SFDC_CONTACT_ID IS NOT NULL
    AND S.ROW_ID IS NOT NULL
    AND S.CON_CD IN (‘Producer’, ‘Firm Contact’, ‘Employee’)
    AND SF.SIEBELCONTACTROWID_C IS NULL;

I have tried googling this issue and still can’t find a good explanation of the cause of this error. Can anyone explain to me what I am doing wrong or provide me with a proper reference for the solution to this issue?

Please and thanks,
RMSBOUSR

RMSBOUSR (BOB member since 2017-09-20)

2

Have to tried to run this query in backend database and checked

surya_sudheer :india: (BOB member since 2006-11-01)

3

Try getting rid of the semi colons at the end of each derived table.

Nick Daniels :uk: (BOB member since 2002-08-15)

4

@surya_sudheer Yes, I have run all the queries in the backend db and they all run fine.

@Nick Daniels I just tried that and it seems to have worked for the time being. I will post back later today to tell you guys if the issue is resolved.

Thanks for suggestions!

RMSBOUSR (BOB member since 2017-09-20)

5

Thanks again, no more issues…yet. Resolved.

RMSBOUSR (BOB member since 2017-09-20)