ORA-00904 when trying to query data via OBIEE

435 views Asked by At

I have a view with the below SQL working and pulling data in Oracle SQL developer, but when I import this view in to the OBIEE repository and try to view data I get the error above. Originally the alias was "Segment" but I thought it might have been a Oracle reserved word. I changed it to "Segment_ID" and then to just "SEG" and neither of them have worked.

CREATE OR REPLACE FORCE VIEW "EPM_FCM_STG"."ARM_PROCESS_MONITOR" ("PERIOD", "ACCOUNT_ID", "Company", "Account", "DESCRIPTION", "SRC_SYS_BAL_BUCKET", "BAL_EXP_BUCKET", "SUBSYS_BAL_BUCKET", "ADJ_SUBSYS_BUCKET", "PREPARER_FREQUENCY", "END_DATE_ACTUAL", "PREPARER", "REVIEWER_1", "REVIEWER_2", "NORMAL_BALANCE", "Seg") AS 
  SELECT R_PeriodEO.PERIOD_NAME                                        AS "PERIOD",
  ReconciliationEO.RECONCILIATION_ACCOUNT_ID                         AS "ACCOUNT_ID",
  R_AttributeValueEO1A.VALUE_TEXT                                    AS "Company",
  R_AttributeValueEO2A.VALUE_TEXT                                    AS "Account",
  ReconciliationEO.RECONCILIATION_DESCRIPTION                        AS "DESCRIPTION",
  R_BalanceSummaryEO3A.AMOUNT                                        AS "SRC_SYS_BAL_BUCKET",
  R_TransactionSummaryEO4A.AMOUNT                                    AS "BAL_EXP_BUCKET",
  R_BalanceSummaryEO5A.AMOUNT                                        AS "SUBSYS_BAL_BUCKET",
  R_TransactionSummaryEO6A.AMOUNT                                    AS "ADJ_SUBSYS_BUCKET",
  R_FrequencyEO8A.FREQUENCY_NAME                                     AS "PREPARER_FREQUENCY",
  TO_CHAR(ReconciliationEO.ACTUAL_END_DATE, 'YYYY-MM-DD"T"hh:mi:ss') AS "END_DATE_ACTUAL",
  (
  (SELECT
    CASE
      WHEN FCM_USERS.FIRST_NAME IS NULL
      AND FCM_USERS.LAST_NAME   IS NULL
      THEN FCM_USERS.USER_LOGIN
      ELSE FCM_USERS.FIRST_NAME
        || ' '
        || FCM_USERS.LAST_NAME
    END
  FROM FCM_USERS
  WHERE FCM_USERS.USER_ID = (COALESCE(R_PreparerEO.ACTIVE_USER_ID, R_PreparerEO.USER_ID))
  )) AS "PREPARER",
  (
  (SELECT
    CASE
      WHEN FCM_USERS.FIRST_NAME IS NULL
      AND FCM_USERS.LAST_NAME   IS NULL
      THEN FCM_USERS.USER_LOGIN
      ELSE FCM_USERS.FIRST_NAME
        || ' '
        || FCM_USERS.LAST_NAME
    END
  FROM FCM_USERS
  WHERE FCM_USERS.USER_ID = (COALESCE(R_ReviewerEO1R.ACTIVE_USER_ID, R_ReviewerEO1R.USER_ID))
  )) AS "REVIEWER_1",
  (
  (SELECT
    CASE
      WHEN FCM_USERS.FIRST_NAME IS NULL
      AND FCM_USERS.LAST_NAME   IS NULL
      THEN FCM_USERS.USER_LOGIN
      ELSE FCM_USERS.FIRST_NAME
        || ' '
        || FCM_USERS.LAST_NAME
    END
  FROM FCM_USERS
  WHERE FCM_USERS.USER_ID = (COALESCE(R_ReviewerEO2R.ACTIVE_USER_ID, R_ReviewerEO2R.USER_ID))
  )) AS "REVIEWER_2",
  CASE ReconciliationEO.NORMAL_BALANCE
    WHEN 'C'
    THEN 'CREDIT'
    ELSE
      CASE ReconciliationEO.NORMAL_BALANCE
        WHEN 'D'
        THEN 'DEBIT'
        ELSE
          CASE ReconciliationEO.NORMAL_BALANCE
            WHEN 'E'
            THEN 'EITHER_DEBIT_OR_CREDIT'
            ELSE 'N/A'
          END
      END
  END                              AS "NORMAL_BALANCE",
  R_AttributeValueEO15A.VALUE_TEXT AS "Seg"
FROM ARM_RECONCILIATIONS ReconciliationEO
LEFT OUTER JOIN ARM_PERIODS R_PeriodEO
ON (ReconciliationEO.PERIOD_ID = R_PeriodEO.PERIOD_ID)
LEFT OUTER JOIN ARM_ATTRIBUTE_VALUES R_AttributeValueEO1A
ON ReconciliationEO.RECONCILIATION_ID  = R_AttributeValueEO1A.OBJECT_ID
AND (R_AttributeValueEO1A.ATTRIBUTE_ID = 100000000027005)
LEFT OUTER JOIN ARM_ATTRIBUTE_VALUES R_AttributeValueEO2A
ON ReconciliationEO.RECONCILIATION_ID  = R_AttributeValueEO2A.OBJECT_ID
AND (R_AttributeValueEO2A.ATTRIBUTE_ID = 100000000027021)
LEFT OUTER JOIN ARM_BALANCE_SUMMARIES R_BalanceSummaryEO3A
ON ReconciliationEO.RECONCILIATION_ACCOUNT_ID = R_BalanceSummaryEO3A.PROFILE_ID
AND R_BalanceSummaryEO3A.PERIOD_ID            = ReconciliationEO.PERIOD_ID
AND (R_BalanceSummaryEO3A.BUCKET_ID           = 100003
AND R_BalanceSummaryEO3A.BALANCE_TYPE         = 1
AND 1                                         = 1)
LEFT OUTER JOIN ARM_TRANSACTION_SUMMARIES R_TransactionSummaryEO4A
ON ReconciliationEO.RECONCILIATION_ID            = R_TransactionSummaryEO4A.RECONCILIATION_ID
AND (R_TransactionSummaryEO4A.CURRENCY_BUCKET_ID = 100003
AND R_TransactionSummaryEO4A.TRANSACTION_TYPE    = 'BEX'
AND 1                                            = 1)
LEFT OUTER JOIN ARM_BALANCE_SUMMARIES R_BalanceSummaryEO5A
ON ReconciliationEO.RECONCILIATION_ACCOUNT_ID = R_BalanceSummaryEO5A.PROFILE_ID
AND R_BalanceSummaryEO5A.PERIOD_ID            = ReconciliationEO.PERIOD_ID
AND (R_BalanceSummaryEO5A.BUCKET_ID           = 100003
AND R_BalanceSummaryEO5A.BALANCE_TYPE         = 2
AND 1                                         = 1)
LEFT OUTER JOIN ARM_TRANSACTION_SUMMARIES R_TransactionSummaryEO6A
ON ReconciliationEO.RECONCILIATION_ID            = R_TransactionSummaryEO6A.RECONCILIATION_ID
AND (R_TransactionSummaryEO6A.CURRENCY_BUCKET_ID = 100003
AND R_TransactionSummaryEO6A.TRANSACTION_TYPE    = 'SUB'
AND 1                                            = 1)
LEFT OUTER JOIN ARM_ACCESS R_PreparerEO
ON ReconciliationEO.RECONCILIATION_ID = R_PreparerEO.OBJECT_ID
AND (R_PreparerEO.ACCESS_TYPE         = 'P')
LEFT OUTER JOIN ARM_FREQUENCIES R_FrequencyEO8A
ON (R_PreparerEO.FREQUENCY_ID = R_FrequencyEO8A.FREQUENCY_ID)
LEFT OUTER JOIN ARM_ACCESS R_ReviewerEO1R
ON ReconciliationEO.RECONCILIATION_ID = R_ReviewerEO1R.OBJECT_ID
AND (R_ReviewerEO1R.ACCESS_TYPE       = 'R'
AND R_ReviewerEO1R.ACCESS_ORDER       = 1)
LEFT OUTER JOIN ARM_ACCESS R_ReviewerEO2R
ON ReconciliationEO.RECONCILIATION_ID = R_ReviewerEO2R.OBJECT_ID
AND (R_ReviewerEO2R.ACCESS_TYPE       = 'R'
AND R_ReviewerEO2R.ACCESS_ORDER       = 2)
LEFT OUTER JOIN ARM_ATTRIBUTE_VALUES R_AttributeValueEO15A
ON ReconciliationEO.RECONCILIATION_ID   = R_AttributeValueEO15A.OBJECT_ID
AND (R_AttributeValueEO15A.ATTRIBUTE_ID = 100000000003025)
WHERE ReconciliationEO.PERIOD_ID       IS NOT NULL;

The error I get is:

Error I'm getting in the OBIEE repository

0

There are 0 answers