2 Replies Latest reply on Jun 2, 2016 12:29 PM by Thomas Dodds

    ORA-00907: missing right parenthesis

    user9041078
    user9041078 Jun 1, 2016 7:34 AM

      I use following query as a datamodel in OBIEE 11.1.1.6.0


      i'm getting missing right parenthesis but this works well in sql developer. please advise


      i need to input parameter as 'first day of month and getting last month end date'

       

      SELECT c.CASE_ID , NVL(C.CASE_AMOUNT,0)"CASE_AMOUNT", C.CASE_NO, C.CONTRACT_NO, C.CASE_TYPE_ID,

      TO_CHAR(C.CASE_FILED_DATE,'DD/MM/YYYY')"CASE_FILED_DATE",C.DATE_KEY, C.LOCCODE,'BAL_FRM_LM'"STATUS"

      FROM DM__LEGAL_CASE C , DM__CASE_HISTORY H

      WHERE C.CASE_ID = H.CASE_ID AND C.DATE_KEY = H.DATE_KEY AND C.LOCCODE = H.LOCCODE

      AND H.CASE_TYPE_ID = '101' AND H.SYS_DATE <= last_day(ADD_MONTHS(:THIS_MNTH_ST,-1)) AND

      H.CASE_ID NOT IN (SELECT CASE_ID FROM DM__CASE_HISTORY

      WHERE CASE_TYPE_ID <> '101'  AND LOCCODE = C.LOCCODE AND SYS_DATE <= last_day(ADD_MONTHS(:THIS_MNTH_ST,-1)))

        • 1. Re: ORA-00907: missing right parenthesis
          rmoff
          rmoff Jun 1, 2016 7:58 AM (in response to user9041078)

          Are you talking about BI Publisher data model? If so, try BI Publisher forum

          • 2. Re: ORA-00907: missing right parenthesis
            Thomas Dodds
            Thomas Dodds Jun 2, 2016 12:29 PM (in response to user9041078)

            try using this version of your query:

             

            SELECT c.CASE_ID ,

              NVL(C.CASE_AMOUNT,0) AS CASE_AMOUNT,

              C.CASE_NO,

              C.CONTRACT_NO,

              C.CASE_TYPE_ID,

              TO_CHAR(C.CASE_FILED_DATE,'DD/MM/YYYY') AS CASE_FILED_DATE ,

              C.DATE_KEY,

              C.LOCCODE,

              'BAL_FRM_LM' AS STATUS

            FROM DM__LEGAL_CASE C ,

              DM__CASE_HISTORY H

            WHERE C.CASE_ID    = H.CASE_ID

            AND C.DATE_KEY     = H.DATE_KEY

            AND C.LOCCODE      = H.LOCCODE

            AND H.CASE_TYPE_ID = '101'

            AND H.SYS_DATE    <= last_day(ADD_MONTHS(:THIS_MNTH_ST,-1))

            AND H.CASE_ID NOT IN

              (SELECT CASE_ID

              FROM DM__CASE_HISTORY

              WHERE CASE_TYPE_ID <> '101'

              AND LOCCODE         = C.LOCCODE

              AND SYS_DATE       <= last_day(ADD_MONTHS(:THIS_MNTH_ST,-1))

              )

             

             

            Use the keyword AS when aliasing your columns ... I don't get a paren missing error when checking the SQL