Oracle DB 12.1.0.2
Hello Team,
may I know how to group by concatenated field GBALID? This field will serve as a unique key for the table.
CREATE MATERIALIZED VIEW OFSTRIM.MV_BI_GL_BALANCES_V2
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS(
SELECT (GLB.BUDGET_VERSION|| GLC.SEGMENT1|| GLC.SEGMENT2|| GLC.SEGMENT3|| GLC.SEGMENT4|| GLC.SEGMENT5) AS GLID ,GLB.ROWID AS GLB_ROWID ,GLC.ROWID AS GLC_ROWID ,GLG.ROWID AS GLG_ROWID ,
GLB.BUDGET_VERSION_ID BUDGET_VERSION,
GLC.SEGMENT1
COMPANY,
GLC.SEGMENT2
COSTCENTRE,
GLC.SEGMENT3
ACCOUNT,
GLC.SEGMENT4
SUBACCOUNT,
GLC.SEGMENT5
LOB,
GLC.SEGMENT6
PARTNER,
GLC.SEGMENT7
PROJECT,
GLC.SEGMENT8
FLOW,
GLC.SEGMENT9
INTERCOMPANY,
GLC.SEGMENT10
SPARE,
GLC.SUMMARY_FLAG,
GLC.ACCOUNT_TYPE
ACCOUNT_TYPE,
GLB.PERIOD_NAME,
GLB.PERIOD_YEAR,
GLB.PERIOD_NUM,
GLB.ACTUAL_FLAG,
SUM (
NVL (GLB.BEGIN_BALANCE_DR, 0) - NVL (GLB.BEGIN_BALANCE_CR, 0))
BEGINBALANCE,
SUM (NVL (GLB.PERIOD_NET_DR, 0) - NVL (GLB.PERIOD_NET_CR, 0))
PTDBALANCE,
SUM (
NVL (GLB.BEGIN_BALANCE_DR, 0)
- NVL (GLB.BEGIN_BALANCE_CR, 0)
+ NVL (GLB.PERIOD_NET_DR, 0)
- NVL (GLB.PERIOD_NET_CR, 0))
YTDBALANCE,
GLB.CURRENCY_CODE
FROM APPS.GL_BALANCES GLB,
APPS.GL_CODE_COMBINATIONS GLC,
APPS.GL_LEDGERS GLG
-- , APPS.GL_LOOKUPS
WHERE GLC.CODE_COMBINATION_ID = GLB.CODE_COMBINATION_ID
-- AND GL_LOOKUPS.LOOKUP_TYPE = 'ACCOUNT TYPE'
AND GLG.LEDGER_ID = GLB.LEDGER_ID
AND GLG.CURRENCY_CODE = GLB.CURRENCY_CODE
AND GLC.SUMMARY_FLAG = 'N'
AND ACTUAL_FLAG <> 'E'
-- AND GLC.ACCOUNT_TYPE = GL_LOOKUPS.LOOKUP_CODE
GROUP BY (GLB.BUDGET_VERSION|| GLC.SEGMENT1|| GLC.SEGMENT2|| GLC.SEGMENT3|| GLC.SEGMENT4|| GLC.SEGMENT5),GLB.ROWID, GLC.ROWID, GLG.ROWID,GLB.BUDGET_VERSION_ID,
GLC.SEGMENT1,
GLC.SEGMENT2,
GLC.SEGMENT3,
GLC.SEGMENT4,
GLC.SEGMENT5,
GLC.SEGMENT6,
GLC.SEGMENT7,
GLC.SEGMENT8,
GLC.SEGMENT9,
GLC.SEGMENT10,
GLC.ACCOUNT_TYPE,
-- GL_LOOKUPS.DESCRIPTION,
GLC.SUMMARY_FLAG,
GLB.PERIOD_NAME,
GLB.PERIOD_YEAR,
GLB.PERIOD_NUM,
GLB.ACTUAL_FLAG,
GLB.CURRENCY_CODE)
;
Error report -
SQL Error: ORA-00904: "GLID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Regards,
Roshan