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: