ORA-00907: missing right parenthesis

user9041078

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

rmoff

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

Thomas Dodds

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