2

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

  • When you import the view do you have an option or ability to honour the case of the column names? You said you changed it 'to just "SEG"', but the code you've shown has "Seg", not "SEG" - they are not the same thing, and from the error OBIEE is looking for the uppercase version.. (Actually making the view column alias "SEG" might solve the issue, but is that acceptable, since some others are mixed-case? Do any of them have to be? I'd avoid quoted identifiers if at all possible...) – Alex Poole Jan 03 '17 at 16:00
  • Ah yes I was coming back to reply that I think it's due to mixed case field alias. It's now erring out on the next mixed case field after I corrected that one. Also, yeah I'm very new to all of this and this SQL was generated by Oracle ARM and I just copy/pasted it and tweaked it here or there. I've been reading that double quotes in your DDL is pretty bad form. I'll adjust that going forward. Thanks for the pro tip @AlexPoole! – Brett Moore Jan 03 '17 at 16:11
  • 1
    [Oracle recommend not using quoted identifiers](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements008.htm#SQLRF51129). The quotes themselves aren't a problem if the identifiers are uppercase (though personally find it makes things harder to read). But if an identifier is quoted because it's mixed case or wouldn't be valid otherwise; they're a pain because you then *always* have to quote them, everywhere. And that's easy to get wrong, as well as being more typing... – Alex Poole Jan 03 '17 at 16:19
  • 2
    Just to chime in here ... if you're cramming a bunch of joins and business logic into a VIEW on which you're going to build OBIEE analyses, you're probably building your self some pain for the future. Generally, you'd do all that work in the BMM of the OBIEE RPD, not 'hidden' from OBIEE as a VIEW. – Robin Moffatt Jan 04 '17 at 11:29
  • Hello Robin! I am planning to build this out within the physical layer when I know what all fields I need in the subject area. I just did this for now because it was a quick way to start working with the data in OBIEE. I do have instances in this SQL where I have multiple joins between two tables so I'm going to have to take the time to figure out how to get around that in the physical layer. – Brett Moore Jan 05 '17 at 14:08

0 Answers0