-
Query working Oracle 10G not working in 9i
I have created a view in Oracle and compiled. The view got compiled in both Oracle 10G and Oracle 9i but when i tried to query the view it gives ORA-00911 Error in Oracle 9i whereas it worked fine in Oracle 10G. can anyone help me in this regard. i've pasted the view script below. thanks in advance
CREATE OR REPLACE VIEW V_SEC_HOLDINGS_ARV
(MODULE, FUND_CODE, IND_CODE, IND_NAME, ISM_CODE,
ISM_NAME, EQ_ISM_NAME, BETA, HLDQTY, MKTVAL,
MKTPRICE, OS_HLDQTY, OS_MKTVAL, SALQTY, BOOKCOST,
BOOKVAL, PLVALUE)
AS
SELECT 'DEBT' MODULE, FUNDCODE FUND_CODE, -2 IND_CODE, 'Debt' IND_NAME,
-2 ISM_CODE, 'Debt' ISM_NAME, '' EQ_ISM_NAME, 0 AS BETA,
SUM (QTY) HLDQTY, SUM (MVAL) MKTVAL, 0 MKTPRICE,
0 OS_HLDQTY, 0 OS_MKTVAL, 0 SALQTY,0 BOOKCOST,
0 BOOKVAL, 0 PLValue
FROM (SELECT QDP_FUNDCODE FUNDCODE, SUM (QDP_HLDQTY) QTY,
SUM (QDP_HLDQTY * QDP_MKTPRICE) MVAL
FROM QUANTIS_DAILY_POSITIONS
WHERE QDP_ASONDATE = TRUNC (SYSDATE)
AND QDP_MODULE IN ('DEBT', 'FD')
AND QDP_HLDQTY > 0
GROUP BY QDP_FUNDCODE
UNION
SELECT DS_FUND_ID FUNDCODE, SUM (DS_QUANTITY) QTY,
SUM (DS_DEALVALUE) MVAL
FROM PRIMARYDEALS
WHERE DS_RECORD_STATUS = 1
AND DS_APPROVAL_FLAG = 2
AND DS_REVERSAL_FLAG = 'N'
AND DS_TRADEDATE <= TRUNC (SYSDATE)
AND (DS_SECURITY_CODE, DS_FUND_ID, DS_DEALNO) NOT IN (
SELECT DISTINCT PA_SECID, PA_FUNDID, PA_DEALNO
FROM PRIMARY_ALLOTMENT
WHERE PA_RECORD_STATUS = 1
AND PA_APPROVAL_FLAG = 2)
GROUP BY DS_FUND_ID
UNION
SELECT RT_FUND_ID FUNDCODE, SUM (RT_QUANTITY) QTY,
SUM (RT_TOTALPRICE) MVAL
FROM REPOTRADES
WHERE RT_TRADEDATE <= TRUNC (SYSDATE)
AND RT_REVERSAL_DATE > TRUNC (SYSDATE)
AND RT_FUND_TYPE = 'FUND'
AND RT_RECORD_STATUS = 1
AND RT_APPROVAL_FLAG = 2
AND RT_REVERSAL_FLAG = 'N'
GROUP BY RT_FUND_ID
UNION
SELECT CBLO_FUNDID FUNDCODE,
SUM (DECODE (CBLO_TRANSTYPE,
'BUY', CBLO_FACEVALUE / 100,
'SELL', (CBLO_FACEVALUE * -1) / 100
)
) QTY,
SUM (DECODE (CBLO_TRANSTYPE,
'BUY', CBLO_FACEVALUE,
'SELL', CBLO_FACEVALUE * -1
)
) MVAL
FROM CBLO_TRADES
WHERE CBLO_FUNDID <> 1
AND CBLO_RECORD_STATUS = 1
AND CBLO_APPROVAL_FLAG = 2
AND CBLO_REVERSAL_FLAG = 'N'
AND CBLO_TRADEDATE <= TRUNC (SYSDATE)
AND CBLO_MATURITY > TRUNC (SYSDATE)
GROUP BY CBLO_FUNDID)
GROUP BY FUNDCODE
UNION
SELECT 'MUTUAL FUND' MODULE, QDP_FUNDCODE FUNDCODE, -3 IND_CODE,
'Mutual Fund' IND_NAME, -3 ISM_CODE, 'Mutual Fund' ISM_NAME,
'' EQ_ISM_NAME, 0 AS BETA, SUM (QDP_HLDQTY) HLDQTY,
SUM (QDP_HLDQTY * QDP_MKTPRICE) MKTVAL, 0 MKTPRICE,
0 OS_HLDQTY, 0 OS_MKTVAL, 0 SALQTY, 0 BOOKCOST,
0 BOOKVAL, 0 PLValue
FROM QUANTIS_DAILY_POSITIONS
WHERE QDP_ASONDATE = TRUNC (SYSDATE)
AND QDP_MODULE = 'MFUNITS'
AND QDP_HLDQTY > 0
GROUP BY QDP_FUNDCODE
union
SELECT QDP_MODULE MODULE, QDP_FUNDCODE FUND_CODE,
NVL (IND_CODE, -1) IND_CODE,
DECODE (IND_CODE,
NULL, 'Index Derivatives',
INITCAP (IND_NAME)
) IND_NAME,
NVL (ISM_CODE, -1) ISM_CODE, INITCAP (ISM_NAME) ISM_NAME,
DECODE (QDP_MODULE, 'EQUITY', ISM_NAME, QDP_ISMNAME) EQ_ISM_NAME,
DECODE (QDP_MODULE, 'EQUITY', NVL (EQB_BETA_DATA, 0), 0) BETA,
QDP_HLDQTY HLDQTY, (QDP_HLDQTY * QDP_MKTPRICE) MKTVAL,
QDP_MKTPRICE MKTPRICE, QDP_OS_HLDQTY OS_HLDQTY,
(QDP_OS_HLDQTY * QDP_MKTPRICE) OS_MKTVAL, QDP_SALEABLE_QTY SALQTY,
QDP_BOOKCOST BOOKCOST, (QDP_HLDQTY * QDP_BOOKCOST) BOOKVAL,
(QDP_HLDQTY * (QDP_MKTPRICE - QDP_BOOKCOST)) PLValue
FROM QUANTIS_DAILY_POSITIONS,
(SELECT EQ_ISM_CODE ISM_CODE, EQ_ISM_SHORT_NAME ISM_NAME,
EQ_ISM_CPM_CODE CMP_CODE
FROM EQ_INSTRUMENT_MASTER
WHERE EQ_ISM_RECORD_STATUS = 1
UNION ALL
SELECT BENCHMARK_CODE ISM_CODE, BNC_SHORTNAME, -1 AS CMP_CODE
FROM BENCHMARK
WHERE BNC_RECORD_STATUS = 1) UL,
(SELECT CPM_CODE, CPM_NAME, CPM_INDUSTRYCODE
FROM COMPANY_MASTER
WHERE CPM_RECORD_STATUS = 1) CM,
(SELECT IND_CODE, IND_NAME
FROM INDUSTRY_MASTER
WHERE IND_RECORD_STATUS = 1) IM,
(SELECT EQB_ASONDATE, EQB_ISMCODE, EQB_BETA_DATA
FROM EQ_SCRIP_BETA OEB
WHERE EQB_RECORD_STATUS = 1
AND EQB_ASONDATE =
(SELECT MAX (EQB_ASONDATE)
FROM EQ_SCRIP_BETA
WHERE EQB_RECORD_STATUS = 1
AND EQB_ISMCODE = OEB.EQB_ISMCODE)) ESB
WHERE QDP_ASONDATE = TRUNC (SYSDATE)
AND QDP_MODULE IN ('EQUITY', 'FUTURES', 'OPTIONS')
AND QDP_ISMCODE = UL.ISM_CODE(+)
AND UL.CMP_CODE = CM.CPM_CODE(+)
AND CM.CPM_INDUSTRYCODE = IM.IND_CODE(+)
AND QDP_ISMCODE = ESB.EQB_ISMCODE(+);
-
Post whole error stack.
Also describe both Ora10g and Ora9i databases including character sets.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
thnx a lot PAVB for your prompt reply. i have given the requested details below,
ORacle 9i - Parameters
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.1.0
Oracle 10G Parameters
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
I hope this information hold good for you. when i was trying to query the specific view i got the following Errors, ORA-00903: invalid table name or ORA-00911: invalid character. i was completely clueless why this happens. Please help me in this regard. thanks in advance
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|